MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

PostgreSQL Table Function

without comments

A quick tutorial on how to write a PL/pgSQL Table function. The functions is simple. It returns the list of conquistadors that were originally German. It does that by filtering on the lang column in the table. For example, you use ‘de‘ for German.

I’ll stage this with the same conquistador table used in the last post. Don’t forget to use the chcp command to the Active Console Code Page to 4-byte Unicode before you run the script file, like:

chcp 65001

Then, connect to the psql shell and run the following script file:

/* Conditionally drop the conquistador table. */
DROP TABLE IF EXISTS conquistador;
 
/* Create the conquistador table. */
CREATE TABLE conquistador
( conquistador_id   SERIAL
, conquistador      VARCHAR(30)
, actual_name       VARCHAR(30)
, nationality       VARCHAR(30)
, lang              VARCHAR(2));
 
/* Insert some conquistadors into the table. */
INSERT INTO conquistador
( conquistador
, actual_name
, nationality
, lang )
VALUES
 ('Juan de Fuca','Ioánnis Fokás','Greek','el')
,('Nicolás de Federmán','Nikolaus Federmann','German','de')
,('Sebastián Caboto','Sebastiano Caboto','Venetian','it')
,('Jorge de la Espira','Georg von Speyer','German','de')
,('Eusebio Francisco Kino','Eusebius Franz Kühn','Italian','it')
,('Wenceslao Linck','Wenceslaus Linck','Bohemian','cs')
,('Fernando Consag','Ferdinand Konšcak','Croatian','sr')
,('Américo Vespucio','Amerigo Vespucci','Italian','it')
,('Alejo García','Aleixo Garcia','Portuguese','pt');

Now, you can build another script file to create the getConquistador function, like:

/* Drop the funciton conditionally. */
DROP FUNCTION IF EXISTS getConquistador;

Create the getConquistador function:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

CREATE FUNCTION getConquistador (IN lang_in VARCHAR(2))
  RETURNS TABLE
    ( conquistador      VARCHAR(30)
    , actual_name       VARCHAR(30)
    , nationality       VARCHAR(30)) AS
$$
BEGIN
  RETURN QUERY
  SELECT c.conquistador
  ,      c.actual_name
  ,      c.nationality
  FROM   conquistador c
  WHERE  c.lang = lang_in;
END;
$$ LANGUAGE plpgsql;

Then, you can test it like:

SELECT * FROM getConquistador('de');

It will return the following:

     conquistador      |    actual_name     | nationality
-----------------------+--------------------+-------------
 Nicolás de Federmán   | Nikolaus Federmann | German
 Jorge de la Espira    | Georg von Speyer   | German
(2 rows)

As always, I hope this helps with a technique that’s useful.

Written by maclochlainn

May 28th, 2022 at 5:59 pm

Posted in Database,DBA,DevOps,Linux,Microsoft Windows 10,Microsoft Windows 11,PL/pgSQL,Postgres,PostgreSQL,PostgreSQL 14,psql,Unix,Windows 10

Tagged with ,

«
»