Postgres stored procedure pl sql - ghdrako/doc_snipets GitHub Wiki

A function is part of a normal SQL statement and is not allowed to start or commit transactions.

In contrast, a procedure can control transactions and even run multiple transactions one after the other. However, you cannot run it inside a SELECT statement. Instead, you have to invoke CALL.

Code of the function is passed to PostgreSQL as a string (') or $$.

CREATE OR REPLACE FUNCTION mysum(int, int)
RETURNS int AS
'
    SELECT $1 + $2;
' LANGUAGE 'sql'; 
-- two languages have assigned a meaning to $$. In Perl, as well as in Bash scripts, $$ represents the process ID.   
CREATE OR REPLACE FUNCTION mysum(int, int)
RETURNS int AS
$$
    SELECT $1 + $2;
$$ LANGUAGE 'sql';
--  
CREATE OR REPLACE FUNCTION mysum(int, int)
RETURNS int AS
$body$
    SELECT $1 + $2;
$body$ LANGUAGE 'sql';

create or replace function get_all_albums 
( 
 in artistid bigint, 
 out album text, 
 out duration interval  
) 
returns setof record  
language sql 
as $$ 
 select album.title as album, 
        sum(milliseconds) * interval '1 ms' as duration 
   from album  
   join artist using(artistid) 
   left join track using(albumid) 
  where artist.artistid = get_all_albums.artistid 
  group by album  
  order by album;
$$;

This function is written in PL/SQL, so it’s basically a SQL query that accepts parameters. To run it, simply do as follows:

select * from get_all_albums(127);

select * 
 from get_all_albums( 
  (select artistid 
     from artist 
    where name = 'Red Hot Chili Peppers'));

select album, duration 
 from artist, 
 lateral get_all_albums(artistid) 
 where artist.name = 'Red Hot Chili Peppers';

function inlining

numeric AS
$$
   SELECT log(2, $1);
$$
LANGUAGE 'sql' IMMUTABLE;

Anonymous code blocks

don’t take parameters and are not permanently stored in the database since they don’t have names.

test=# DO
$$
    BEGIN
        RAISE NOTICE 'current time: %', now();
    END;
$$ LANGUAGE 'plpgsql';
NOTICE:  current time: 2022-11-10 11:18:08.640424+01
DO