PL/pgSQL

CREATE OR REPLACE FUNCTION

My Style

RETURN vs OUT parameters

Postgresql functions can either be written

CREATE OR REPLACE FUNCTION add(a integer, b integer) RETURNS integer
LANGUAGE plpgsql
AS $code$
BEGIN
RETURN a + b;
END;
$code$;

Alternatively, it can be written in a more Prologish style with parameters labeled IN or OUT (or INOUT).

CREATE OR REPLACE FUNCTION add(IN a integer, IN b integer, OUT c integer)
LANGUAGE plpgsql
AS $code$
BEGIN
c = a + b;
END;
$code$;

Since most of the functions for my project involve message broker patterns message_broker(IN json_in JSONB, OUT json_out JSONB), I find the Prologish style better.

LANGUAGE

The examples in the documentation tend to put LANGUAGE plpgsql at the end. I find it more logical to put that line ahead of the code block.

Alternative languages to PL/pgSQL include plain vanilla SQL, Tcl, Perl and Python. However, these aren’t included by default, so plpgsql seems the easiest Procedural Languages option.

AS $code$ block

Dollar-Quoted String Constants

A dollar-quoted string constant consists of a dollar sign ($), an optional “tag” of zero or more characters, another dollar sign, an arbitrary sequence of characters that makes up the string content, a dollar sign, the same tag that began this dollar quote, and a dollar sign.

Need to GRANT pg_execute_server_program TO myusername;

https://stackoverflow.com/questions/78739142/how-do-i-pass-json-to-a-postgres-copy-tmp-from-program-command

CREATE OR REPLACE FUNCTION json_func(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 'echo ''%s'' | jq -c .' $bash$, json_in);
SELECT json_tmp FROM tmp INTO json_out;
DROP TABLE tmp;
END;
$code_block$;

SELECT json_func('{"key1": "val1", "key2": "val2"}'::JSONB);

SELECT message_broker('{"action": "new_game", "game": "tictactoe"}'::JSONB);

format