James A Rosen
James A Rosen
PostsComments
DTDrizzle Team
Created by James A Rosen on 1/1/2025 in #help
Migrations with User-Defined Functions
My current thinking is 1. store the user-defined functions in a directory like udf/ 2. write a script that compares the each function in the directory to the same definition in the database; if they differ, generate a new custom migration to update 3. apply migrations There are some challenges with this approach: * if a function's signature changes, we need to drop and re-create, not just create if not exists * the database normalizes function contents, including changing casing and spacing
3 replies
DTDrizzle Team
Created by z3n on 9/29/2023 in #help
Getting syntax error when using the sql operator (pg)
Following up on this old thread… Postgres allows setting a setting with the SET keyword: 
set foo.bar = 'baz';
select current_setting('foo.bar');
-- | current_setting |
-- |             baz |
set foo.bar = 'baz';
select current_setting('foo.bar');
-- | current_setting |
-- |             baz |
But you can't put SET in a prepared statement: 
PREPARE set_foo_bar (text) AS
set foo.bar to $1;
-- ERROR:  syntax error at or near "set"
PREPARE set_foo_bar (text) AS
set foo.bar to $1;
-- ERROR:  syntax error at or near "set"
Postgres also allows setting a setting with the set_config function. The third argument is whether the setting is scoped to the transaction (true) or to the session (false): 
select set_config('foo.bar', 'baz', true);
select set_config('foo.bar', 'baz', true);
Because it's a function, you can put this in a prepared statement: 
PREPARE set_foo_bar (text) AS
select set_config('foo.bar', $1, true);
PREPARE set_foo_bar (text) AS
select set_config('foo.bar', $1, true);
Because web applications tend to reuse one Postgres session for many end-users, I recommend passing true for the third argument. That means you must wrap your work in a transaction. Otherwise, the setting will disappear before your next statement!
8 replies