Postgres stored procedure sql - ghdrako/doc_snipets GitHub Wiki

• Inlining of SQL functions

Główną zaletą czystych funkcji SQL jest, jeśli są one dość proste, ich integracja podczas wewnętrznego przepisywania zapytania (inlining): nie są one zatem "czarnymi skrzynkami" dla optymalizatora. I odwrotnie, optymalizator nie wie nic o zawartości funkcji PL/pgSQL.

W poniższym przykładzie funkcja służy jako filtr zapytania. Podobnie jak w czystym SQL, pozwala na użycie indeksu w kolumnie date_embauche poza tabelą employes_big:

CREATE FUNCTION employe_eligible_prime_sql (service int, date_embauche date)
 RETURNS boolean
 LANGUAGE sql
 AS $$
 SELECT ( service !=3 AND date_embauche < '2003-01-01') ;
 $$ ;
 employes_big
 EXPLAIN (ANALYZE) SELECT matricule, num_service, nom, prenom
 FROM
 WHERE
 employe_eligible_prime_sql (num_service, date_embauche) ;
 QUERY PLAN--------------------------------------------------------------------------------
Index Scan using employes_big_date_embauche_idx on employes_big
 (cost=0.42..1.54 rows=1 width=22) (actual time=0.008..0.009 rows=1 loops=1)
 Index Cond: (date_embauche < '2003-01-01'::date)
 Filter: (num_service <> 3)
 Rows Removed by Filter: 1
 Planning Time: 0.102 ms
 Execution Time: 0.029 ms

W przypadku wersji tej samej funkcji w PL/pgSQL harmonogram nie widział indeksowanego kryterium. Nie ma innego wyjścia, jak tylko przeczytać całą tabelę i wywołać funkcję dla każdego wiersza, która jest oczywiście wolniejsza:

CREATE FUNCTION employe_eligible_prime_pl (service int, date_embauche date)
 RETURNS boolean
 LANGUAGE plpgsql AS $$
 BEGIN
 RETURN ( service !=3 AND date_embauche < '2003-01-01') ;
 END ;
 $$ ;
 EXPLAIN (ANALYZE) SELECT matricule, num_service, nom, prenom
 FROM employes_big
 WHERE employe_eligible_prime_pl (num_service, date_embauche) ;
 ------------------------------------QUERY PLAN--------------------------------------------
Seq Scan on employes_big (cost=0.00..134407.90 rows=166338 width=22)
 (actual time=0.069..269.121 rows=1 loops=1)
 Filter: employe_eligible_prime_pl(num_service, date_embauche)
 Rows Removed by Filter: 499014
 Planning Time: 0.038 ms
 Execution Time: 269.157 ms

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