Dynamic table name about pggen HOT 3 CLOSED

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:

from pggen.

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!

from pggen.

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.

from pggen.