Database seeding with Sqitch migrations

Question

I have a set of Sqitch migrations that create tables and functions etc. However, I also need to seed my database with a basic data set required by my app.

However, some of the seeds require Ids from seeds created prior. E.g. I have a function to create a Post and a function to a Comment, after I create a Post I then need to create the Comment referencing the Id of the Post.

My post_create function looks like this:

BEGIN;

  CREATE FUNCTION post_create(
    _title TEXT,
    _body TEXT
  )
  RETURNS SETOF post_type
  LANGUAGE plpgsql
  AS $$
    BEGIN
      RETURN QUERY (
        WITH _created_post AS (  
          INSERT INTO "post" (
            "title",
            "body"
            "created_at"
          )
          VALUES (
            _title,
            _body,
            ROUND(EXTRACT(EPOCH FROM now()))
          )
          RETURNING
            *
        )
        SELECT
          *
        FROM
          _created_post
      );
    END;
  $$;

COMMIT;

And my comment_create function looks similar, like this:

BEGIN;

  CREATE FUNCTION comment_create(
    _post_id INTEGER,
    _body TEXT
  )
  RETURNS SETOF comment_type
  LANGUAGE plpgsql
  AS $$
    BEGIN
      RETURN QUERY (
        WITH _created_comment AS (  
          INSERT INTO "comment" (
            "post_id",
            "body"
            "created_at"
          )
          VALUES (
            _post_id,
            _body,
            ROUND(EXTRACT(EPOCH FROM now()))
          )
          RETURNING
            *
        )
        SELECT
          *
        FROM
          _created_comment
      );
    END;
  $$;

COMMIT;

My seeding migration is basically a blank Sqitch migration:

-- Deploy my-app:seeds to pg
-- requires: post_create
-- requires: comment_create

BEGIN;

  -- Create a post and capture the post Id
  -- Create a comment with previously captured post Id

COMMIT;

However, I'm having trouble figuring out the syntax to make this work correctly.

How can I make my Sqitch migration script call functions and use the result as input when calling other functions?

Adrian Klaver · Accepted Answer · 2020-06-08T02:32:41.730

1

Use an anonymous function?: https://www.postgresql.org/docs/12/sql-do.html

DO $$
DECLARE
    post post_type;
BEGIN
    --Edited to version that Luke created and used.
    SELECT * FROM post_create(...) INTO post;
    PERFORM comment_create(post.id, 'body text');
END$$;

edited Jun 08 '20 at 02:32

answered Jun 07 '20 at 20:44

Adrian Klaver

15,886
2
17
28

Thanks. Unfortunately I get an error: `ERROR: syntax error at or near "SELECT" LINE 5: post = SELECT post_create(`. – Luke Jun 07 '20 at 21:27
Edited back to my original form. – Adrian Klaver Jun 07 '20 at 21:35
I seem to be having more luck with syntax like this: `SELECT * FROM post_create(...) INTO post;`. Just wondering why the syntax in your answer throws an error... – Luke Jun 07 '20 at 22:08
Are you talking this post = post_create('title', 'body text'); version or the post = select * from post_create('title', 'body text'); version? The second version will throw an error because I forgot that on assignment plpgsql will do an implicit select func_name. If you are seeing an error with the first version, what is the error message? – Adrian Klaver Jun 07 '20 at 23:50
Ah. OK. It was unclear to me that the original answer was edited. Yes, the `post_create` in the new version works but the `comment_create` now throws a `ERROR: syntax error at or near "comment_create"`. However, when I add a `PERFORM` in front on it, it works. I guess it wants the output to go somewhere... – Luke Jun 08 '20 at 00:25
Yeah, I have not been writing plpgsql functions much lately and so I had to relearn the subtleties. The alternative to PERFORM would be to declare comment = comment_type and then do what you did with post_create. – Adrian Klaver Jun 08 '20 at 00:44
1

Changed answer code to incorporate your changes, so anyone coming to this will get working example. – Adrian Klaver Jun 08 '20 at 02:34

Database seeding with Sqitch migrations

1 Answers1