Given: CREATE TYPE my_struct AS ( a int4, b text, c float4 ); DO $$ DECLARE my_cursor refcursor; my_value my_struct; BEGIN OPEN my_cursor FOR SELECT (1, 'a', 10.0)::my_struct; FETCH my_cursor INTO my_value; -- ERROR! END $$ Postgres raises an error…
Does anyone have any idea why I get the error? I use t for which I receive, old to compare and up are the new or modified data equally to compare with the old ones. Why do I keep getting…
CREATE OR REPLACE FUNCTION wrapperFunction() RETURNS bytea AS $BODY$ BEGIN SELECT function1() || function2(); END; $BODY$ LANGUAGE plpgsql Is there a way to make calls to function1 and function2 parallel? (they are read only, no side effects and independent)
Is there a way to figure out which statement from the block is currently running in Postgres? (Even extra extensions or tracing might be an option) Below is the quick way to reproduce but in real scenario the block is…
I'm new to Postgres and trying to create code that inserts data from several tables into a single table. All source tables have a name starting with "B3_HIST_", differing only in terms of year (they are named from "B3_HIST_2015" to…
Creatinf icase function using CREATE OR REPLACE FUNCTION public.icase( cond1 boolean, res1 anyelement, cond2 boolean, res2 anyelement, conddefault anyelement) RETURNS anyelement LANGUAGE 'sql' IMMUTABLE PARALLEL UNSAFE AS $BODY$ SELECT CASE WHEN $1 THEN $2 WHEN $3 THEN $4 ELSE $5…
I have a case where I have a table "tab" that stores data (keyed by symbol and time) as well as a second table "summ" that stores the exponential weighted running average of the data in the first table. I…
I've hit a snag with the following PL/pgSQL function I wrote CREATE OR REPLACE FUNCTION swipl_rpc(IN json_in JSONB, OUT json_out JSONB) LANGUAGE plpgsql AS $code_block$ BEGIN CREATE TEMPORARY TABLE tmp (json_tmp JSONB); EXECUTE format($bash$ COPY tmp FROM PROGRAM 'ggp-json ''%s'''…
Let's say I have a function that returns type record: CREATE OR REPLACE FUNCTION pnh_row(myId integer) RETURNS RECORD AS $$ DECLARE myRow RECORD; BEGIN // blah blah return myRow; END; $$ language plpgsql; How can I insert and update this…
I am building a function to insert a record into an audit table every time a record is inserted, updated, or deleted in the parent table. I am struggling to insert one JSON object that is the primary keys of…