MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

PostgreSQL Table Function

without comments

This shows how to write a PL/pgSQL function that returns a filtered table result set while writing to a debug log file. The example requires a small character table, like:

DROP TABLE IF EXISTS CHARACTER;
CREATE TABLE CHARACTER
( character_id    SERIAL
, character_name  VARCHAR );

and, a logger table:

DROP TABLE IF EXISTS logger;
CREATE TABLE logger
( logger_id     SERIAL
, message_text  VARCHAR );

Now, let’s insert a couple rows into the character table. The following query inserts one row:

INSERT INTO CHARACTER
( character_name )
VALUES
('Harry Potter');

It was simply too much fun to write this tricky insert statement to the character table. It only submits a value when it doesn’t already exist in the set of character_name column values. While it eliminates the need for a unique constraint on the character_name column, it makes every insert statement more costly in terms of machine resources.

WITH cte AS
( SELECT 'Harry Potter' AS character_name
  UNION ALL
  SELECT 'Hermione Granger' AS character_name
  UNION ALL
  SELECT 'Ronald Weasily' AS character_name )
INSERT INTO CHARACTER
( character_name )
( SELECT character_name
  FROM   cte
  WHERE  NOT EXISTS
          (SELECT NULL
           FROM   CHARACTER c
           WHERE  c.character_name = cte.character_name));

You can verify these insert statements work with the following query:

SELECT * FROM CHARACTER;

It returns:

 character_id |  character_name  
--------------+------------------
            1 | Harry Potter
            2 | Hermione Granger
            3 | Ronald Weasily
(3 rows)

The following character_query PL/pgSQL function filters table results and returns a table of values. The function defines the future query return results, which is a full fledged object-oriented programming adapter pattern.

CREATE OR REPLACE
  FUNCTION character_query (pattern VARCHAR)
  RETURNS TABLE ( character_id    INTEGER
                , character_text  VARCHAR ) AS
$$
BEGIN
  RETURN QUERY
  SELECT c.character_id
  ,      c.character_name
  FROM   CHARACTER c
  WHERE  c.character_name SIMILAR TO '%'||pattern||'%';
END;
$$ LANGUAGE plpgsql;

You can test the character_query function, like this:

SELECT * FROM character_query('Hermione');

It returns:

 character_id |  character_text  
--------------+------------------
            2 | Hermione Granger
(1 row)

Building on what we did, let’s log our query word in the logger table. You add an insert statement after the BEGIN keyword and before the RETURN QUERY phrases, like:

CREATE OR REPLACE
  FUNCTION character_query (pattern VARCHAR)
  RETURNS TABLE ( character_id    INTEGER
                , character_text  VARCHAR ) AS
$$
BEGIN
  INSERT INTO logger
  ( message_text )
  VALUES
  ( pattern );
 
  RETURN QUERY
  SELECT c.character_id
  ,      c.character_name
  FROM   CHARACTER c
  WHERE  c.character_name SIMILAR TO '%'||pattern||'%';
END;
$$ LANGUAGE plpgsql;

Now let’s test the new character_query function with this test case:

SELECT * FROM character_query('Ron');

Then, let’s check the logger table with this query:

SELECT * FROM logger;

It displays:

 logger_id | message_text 
-----------+--------------
         1 | Hermione
(1 row)

As always, I hope this helps those looking for a solution.

Written by maclochlainn

February 21st, 2020 at 12:58 am

Posted in Linux,Postgres,PostgreSQL

Tagged with ,

«
»