SQL functions … are the better choice: For simple scalar queries. Not much to plan, better save any overhead. For single (or very few) calls per session. Nothing to gain from plan caching via prepared statements that PL/pgSQL has to offer. See below. If they are typically called in the context of bigger queries and … Read more
Use unnest. For example: CREATE OR REPLACE FUNCTION test( p_test text[] ) RETURNS void AS $BODY$ BEGIN SELECT id FROM unnest( p_test ) AS id; END; $BODY$ LANGUAGE plpgsql IMMUTABLE COST 1;
I think the solution to add following to .psqlrc is far from perfection \set ON_ERROR_STOP on there exists much more simple and convenient way – use psql with parameter: psql -v ON_ERROR_STOP=1 better to use also -X parameter turning off .psqlrc file usage. Works perfectly for me p.s. the solution found in great post from … Read more
\df+ <function_name> in psql.
Postgres 12 or newer STORED generated columns are introduced with Postgres 12 – as defined in the SQL standard and implemented by some RDBMS including DB2, MySQL, and Oracle. Or the similar “computed columns” of SQL Server. Trivial example: CREATE TABLE tbl ( int1 int , int2 int , product bigint GENERATED ALWAYS AS (int1 … Read more
\df+ in psql gives you the sourcecode.
GRANT on the database is not what you need. Grant on the tables directly. Granting privileges on the database mostly is used to grant or revoke connect privileges. This allows you to specify who may do stuff in the database if they have sufficient other permissions. You want instead: GRANT ALL PRIVILEGES ON TABLE side_adzone … Read more
Because the number can be up to 15 digits, you’ll need to cast to an 64 bit (8-byte) integer. Try this: SELECT * FROM table WHERE myint = mytext::int8 The :: cast operator is historical but convenient. Postgres also conforms to the SQL standard syntax myint = cast ( mytext as int8) If you have … Read more
Kept having to return here to look up pg_get_viewdef (how to remember that!!), so searched for a more memorable command… and got it: \d+ viewname You can see similar sorts of commands by typing \? at the pgsql command line. Bonus tip: The emacs command sql-postgres makes pgsql a lot more pleasant (edit, copy, paste, … Read more
I accomplished the same goal by using a WITH clause, it’s nowhere near as elegant but can do the same thing. Though for this example it’s really overkill. I also don’t particularly recommend this. WITH myconstants (var1, var2) as ( values (5, ‘foo’) ) SELECT * FROM somewhere, myconstants WHERE something = var1 OR something_else … Read more