Use a temporary table in PostgreSQL. For your example: CREATE TEMP TABLE product_totals ( product_id int , revenue money ); The manual about CREATE TABLE: If specified, the table is created as a temporary table. Temporary tables are automatically dropped at the end of a session, or optionally at the end of the current transaction … Read more
I can’t remember the last time I actually needed to use an explicit cursor for looping in PL/pgSQL. Use the implicit cursor of a FOR loop, that’s much cleaner: DO $$ DECLARE rec record; nbrow bigint; BEGIN FOR rec IN SELECT * FROM pg_tables WHERE tablename NOT LIKE ‘pg\_%’ ORDER BY tablename LOOP EXECUTE ‘SELECT … Read more
There is no BREAK in PL/pgSQL. EXIT terminates the loop. CONTINUE continues at the next iteration of the loop. You can attach a <<label>> to loops and add it as parameter to each of these commands. Then you terminate / continue the labeled loop. Else, it concerns the inner loop. RETURN exits from the function … Read more
Your function has a couple of smallint parameters. But in the call, you are using numeric literals that are presumed to be type integer. A string literal or string constant (‘123’) is not typed immediately. It remains type “unknown” until assigned or cast explicitly. However, a numeric literal or numeric constant is typed immediately. The … Read more
In PL/pgSQL you can SELECT INTO as many variables at once as you like directly. You just had the syntax backwards: SELECT INTO unsolvedNodes, lengths array_agg(DISTINCT idDestination), array_agg(length) FROM road WHERE idOrigin = ANY(solvedNodes) AND NOT (idDestination = ANY(solvedNodes)); You have the keyword INTO followed by a list of target variables, and you have a … Read more
Do it as plain SQL CREATE OR REPLACE FUNCTION tst_dates_func() RETURNS TABLE( date_value date, date_id int, date_desc varchar) as $BODY$ select a.date_value, a.date_id, a.date_desc from dates_tbl a; $BODY$ LANGUAGE sql; If you really need plpgsql use return query CREATE OR REPLACE FUNCTION tst_dates_func() RETURNS TABLE( date_value date, date_id int, date_desc varchar) as $BODY$ BEGIN perform … Read more
DO command vs. PL/pgSQL function A DO command does not return rows. You can send NOTICES or RAISE other messages (using default LANGUAGE plpgsql), or you can write to a (temporary) table and later SELECT from it to get around this. But really, create a function instead, where you can define a return type with … Read more
A plpgsql function automatically runs inside a transaction. It all succeeds or it all fails. The manual: Functions and trigger procedures are always executed within a transaction established by an outer query — they cannot start or commit that transaction, since there would be no context for them to execute in. However, a block containing … Read more
Postgres 9.5 or later IF NOT EXISTS was added to CREATE SEQUENCE in Postgres 9.5. That’s the simple solution now: CREATE SEQUENCE IF NOT EXISTS myschema.myseq; But consider details of the outdated answer anyway … And you know about serial or IDENTITY columns, right? Auto increment table column Postgres 9.4 or older Sequences share the … Read more
PERFORM is plpgsql command used for calls of void functions. PLpgSQL is careful about useless SELECT statements – the SELECT without INTO clause is not allowed. But sometimes you need to call a function and you don’t need to store result (or functions has no result). The function in SQL is called with SELECT statement. … Read more