Dynamic table name

[dnnspaul]

Hi!

I'm trying to integrate pggen into an existing project and am very happy with it so far. But now I have experienced a problem and dont know if it's even possible to be handled by pggen.

For specific data, I'm using dynamic schemes. Every user has its own scheme in the database to kind of separate the data. Before using pggen, my solution was using fmt.Sprintf() and not the prepared statements by pgx:

row = tx.QueryRow(ctx, fmt.Sprintf(`
            INSERT INTO blumo.instance_%v.activities (user_id, category, created_at, outcome, message_id, affected_document_id)
            VALUES ($1, $2, $3, $4, $5, $6)
            RETURNING id
        `, request.InstanceId), request.AuthorId, request.Category.Number(), insertTimestamp, request.Outcome.Number(), request.Message, request.DocumentId)

but trying it with the following sql:

-- name: InsertDocumentActivity :one
INSERT INTO instance_pggen.arg('InstanceId').activities
(user_id, category, created_at, outcome, message_id, affected_document_id)
VALUES
(pggen.arg('UserId'), pggen.arg('Category'), pggen.arg('CreatedAt'), pggen.arg('Outcome'), pggen.arg('MessageId'), pggen.arg('AffectedDocumentId'))
RETURNING id;

I only get ERROR: relation "instance_$1.activities" does not exist (SQLSTATE 42P01), while running pggen, back. And I can understand the problem, but my question is, if there is even a chance to solve this or if pggen is just "not made for this special case". I already tried it with a custom connection string, but didn't manage to solve it with it either.

Does anybody has an idea? Is there any possibility to work-around the prepared statements and inject a variable otherwise? I know it's a bleeding edge case and probably you never heard of anything like that, so I appreciate any help, even the slightest.

[jschaf]

Under the hood, pggen only supports prepared queries. Prepared queries are limited to values and you can't use them for identifiers.

I'd eventually like support dynamic identifiers. There's a few outstanding requests for it. I think I'd make it something like pggen.relation to enable passing in either an identifier or subquery.

For your specific scenario, I'd recommend wrapping the dynamic part in a plpgsql function and call it like so. Calling it in the FROM clause is important so that we get a row containing all the columns instead of 1 row with a single column of a Postgres composite type (a_user1,a_category,"2022-07-17 01:48:40.917932+00",a_outcome,a_message_id).

-- name: InsertDocumentActivity :one
SELECT *
FROM tmp.insert_activity_for_instance(
  _instance := pggen.arg('instance'),
  _user_id := pggen.arg('user'),
  _category := pggen.arg('category'),
  _created_at := pggen.arg('created_at'),
  _outcome := pggen.arg('outcome'),
  _message_id := pggen.arg('message_id')
  );

Here's the test code I used to validate the idea. Each Postgres table also declares a type of the same name which is what we're returning.

CREATE TABLE tmp.instance_foo_activities (
  user_id    text PRIMARY KEY,
  category   text,
  created_at timestamptz,
  outcome    text,
  message_id text
);

CREATE OR REPLACE FUNCTION tmp.insert_activity_for_instance(
  _instance text,
  _user_id text,
  _category text,
  _created_at timestamptz,
  _outcome text,
  _message_id text
) RETURNS setof tmp.instance_foo_activities AS $fn$
DECLARE
  -- NOTE: SQL injection possible here.
  _tbl text = 'tmp.instance_' || _instance || '_activities';
BEGIN
  -- Using %s instead of %I because %s doesn't work with dotted paths with
  -- schemas.
  RETURN QUERY EXECUTE format($$
    INSERT INTO %s (user_id, category, created_at, outcome, message_id)
    VALUES ($1, $2, $3, $4, $5)
    RETURNING *
  $$, _tbl)
    USING _user_id, _category, _created_at, _outcome, _message_id;
END;
$fn$ LANGUAGE plpgsql VOLATILE;

SELECT *
FROM tmp.insert_activity_for_instance(
  _instance := 'foo',
  _user_id := 'a_user',
  _category := 'a_category',
  _created_at := now(),
  _outcome := 'a_outcome',
  _message_id := 'a_message_id'
  );

Returns the following row:

column	value
user_id	a_user
category	a_category
created_at	2022-07-17 01:43:00.480676 +00:00
outcome	a_outcome
message_id	a_message_id

[dnnspaul]

Damn, that was fast. I was a bit scared when the email notification just arrived, because its middle in the night in germany... anyway, thanks for your time. I never got in touch with plpgsql, but will have a look tomorrow. From my first sight that looks very promising. I was messing around with sed the last hours, because I didn't see any other way. Using the native functions of pgsql didn't come to my mind, but like I said, looks promising. Thanks again for your time!

EDIT: @jschaf it looks like there was a misunderstanding. I want to set the scheme name dynamically instead of the table name. But the plpgsql sits on the scheme level as I understand, so can't be used in my case.

Another EDIT: Well. I'm a bit ashamed. Obviously, I can change the SCHEMA with SET search_path TO XYZ, but didn't think about that last night and always tried to solve it within the query. Now I'm starting the transaction, send the search-path query and start using pggen. Works flawlessly. Maybe it was just too late last night ... anyway, thanks for your time, keep up the great work!

[jschaf]

But the plpgsql sits on the scheme level as I understand

It should work okay no matter where the function is defined. The function takes in the table path so you can pass in whatever schema qualified table you want as a string. The function blindy interpolates _instance into an insert statement (hence the comment about sql injection).

SELECT *
FROM tmp.insert_activity_for_instance(
  _instance := 'any.arbitrary.schema.you.want.foo',
  -- rest of the args
  );

Setting the search_path also works but I suspect passing in the schema is easier to manage.