storing an inserted record as a variable within a test

[Matthew Hoopes]

I see that DO blocks are frowned up (link), but i was wondering if it were possible to actually achieve what the poster was trying to do. In his case, he was looking to do

id uuid := uuid_generate_v4()

I'm looking to do something similar, where i have a "factory function", and can store the inserted record in a var to use later. (I also posted this question on stack overflow, if you're into internet points)

Example factory function:

CREATE FUNCTION build_rec_for_test()
RETURNS mytable
AS $$
   INSERT INTO mytable VALUES (

      rand_uuid(), rand_int(), rand_int()

   ) 

   RETURNING *;
$$ LANGUAGE sql;

And i'd like to do something like

BEGIN;
SELECT plan(1);

DECLARE my_rec = build_rec_for_test();

-- perform a test with values from the factory function
SELECT is(SELECT id FROM mytable, my_rec.id);

SELECT * FROM finish();
ROLLBACK;

Is something like this achievable? Or am i just thinking about this all wrong? (I'm porting from a procedural language, fwiw...)

Thanks!

[David E. Wheeler]

On Sep 17, 2024, at 18:31, Matthew Hoopes <matthew...@gmail.com> wrote:

> I'm looking to do something similar, where i have a "factory function", and can store the inserted record in a var to use later. (I also posted this question on stack overflow, if you're into internet points)
>
> Example factory function:
> CREATE FUNCTION build_rec_for_test()
> RETURNS mytable
> AS $$
> INSERT INTO mytable VALUES (
> rand_uuid(), rand_int(), rand_int()
> )
> RETURNING *;
> $$ LANGUAGE sql;

What is it you want to test? That the values inserted into mutable are the same as those returned?

> And i'd like to do something like
>
> BEGIN;
> SELECT plan(1);
>
> DECLARE my_rec = build_rec_for_test();
>
> -- perform a test with values from the factory function
> SELECT is(SELECT id FROM mytable, my_rec.id);
>
> SELECT * FROM finish();
> ROLLBACK;
>
> Is something like this achievable? Or am i just thinking about this all wrong? (I'm porting from a procedural language, fwiw...)

Your DECLARE is used outside a PL/pgSQL block, so I don’t think it’ll work, no. But something like this would:

BEGIN;
SELECT plan(1);

-- perform a test with values from the factory function

WITH my_rec AS (SELECT * FROM build_rec_for_test())
SELECT is(my_rec::mytable, mytable) FROM my_rec, mytable;

SELECT * FROM finish();
ROLLBACK;

HTH,

David

[Matthew Hoopes]

This is great, thanks! (Sorry for the slow reply, work gets in the way...)

Do you know of a way I can use that setup for multiple tests? If I have a bunch of rows to set up, it would be nice to only have to do that setup once.

BEGIN;
SELECT plan(1);

-- perform a test with values from the factory function

WITH 

    my_rec AS (SELECT * FROM build_rec_for_test()),

    my_child_1 AS (SELECT * FROM build_child_for_test(parent_id = (SELECT id FROM my_rec)),

    my_grandchild_1 AS (SELECT * FROM build_grandchild_for_test(parent_id = (SELECT id FROM my_child_1)),

    ...and so on...

SELECT is(

    (SELECT id FROM my_rec),

    (SELECT parent_id FROM my_child_1)

),

--- and here is non-sql, but just something to illustrate the question

SELECT is(

    (SELECT id FROM my_child_1),

    (SELECT parent_id FROM my_grandchild_1)

);

--- and so on...

SELECT * FROM finish();
ROLLBACK;

I'm trying to find a clever way to hide all that in yet another function, and somehow return my_rec, my_child_1, and my_grandchild_1 in a single type, but i don't have that level of postgres knowledge yet...

Thanks again!

[Matthew Hoopes]

10 minutes after I replied, i somehow magically made this work. I'm wondering if you have any comments on it, good or bad:

BEGIN;
SELECT plan(2);

CREATE FUNCTION test_schema.test1()
RETURNS SETOF TEXT
AS $$
DECLARE
  parent1 parent_type;
  parent2 parent_type;
  child1 child_type;
  child2 child_type;
BEGIN

  SELECT * INTO parent1 FROM create_mock_parent();
  SELECT * INTO parent2 FROM create_mock_parent();
  SELECT * INTO child1 FROM create_mock_child(parent_id := parent1.id);
  SELECT * INTO child2 FROM create_mock_child(parent_id := parent2.id);

  RETURN QUERY SELECT is(parent1.id, child1.parent_id);
  RETURN QUERY SELECT is(parent2.id, child2.parent_id);

END;
$$ LANGUAGE plpgsql;

SELECT * FROM test_schema.test1();

SELECT * FROM finish();
ROLLBACK;

[David E. Wheeler]

On Sep 28, 2024, at 20:41, Matthew Hoopes <matthew...@gmail.com> wrote:

> RETURN QUERY SELECT is(parent1.id, child1.parent_id);
> RETURN QUERY SELECT is(parent2.id, child2.parent_id);

Use RETURN NEXT for these:

RETURN NEXT is(parent1.id, child1.parent_id);
RETURN NEXT is(parent2.id, child2.parent_id);

Best,

David