Comments (3)
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 |
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.
Related Issues (20)
- Encoder panics on composite type arrays containing pgtype.Numeric HOT 1
- Consider allowing a flag to decode to more native Go types HOT 2
- Support for JSONB? HOT 3
- Boolean arrays don't support --go-type mapping to []bool
- Commit broke generation of custom types HOT 5
- Generated code for insert func, individual parameters vs single struct HOT 2
- Type override to *time.Time missing time package import HOT 4
- PGX v5 HOT 12
- Nullability seemingly not respected when only one string column is returned HOT 7
- inconsistent return structures generated HOT 1
- Support for In conditions HOT 3
- Error when using bytea datatype HOT 1
- Proposal: Configure struct generation capability HOT 4
- Unexported genericBatch prevents GoMock mockgen on Querier interface HOT 4
- add a `pggen.optional()` directive HOT 5
- possible to support a two-argument pggen.arg eg `pggen.arg(param text, default_value anyelement)`? HOT 9
- Proposal: Add CLI flag to always generate JSON tags for structs HOT 7
- Tracing and Testing HOT 1
- Embedded composite types using a pointer HOT 2
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from pggen.