Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to pass a record to a PL/pgSQL function?

I have 8 similar PL/pgSQL functions; they are used as INSTEAD OF INSERT/UPDATE/DELETE triggers on views to make them writable. The views each combine columns of one generic table (called "things" in the example below) and one special table ("shaped_things" and "flavored_things" below). PostgreSQL's inheritance feature can't be used in our case, by the way.

The triggers have to insert/update rows in the generic table; these parts are identical across all 8 functions. Since the generic table has ~30 columns, I'm trying to use a helper function there, but I'm having trouble passing the view's NEW record to a function that needs a things record as input.

(Similar questions have been asked here and here, but I don't think I can apply the suggested solutions in my case.)

Simplified schema

CREATE TABLE things (
    id    SERIAL  PRIMARY KEY,
    name  TEXT    NOT NULL
    -- (plus 30 more columns)
);
CREATE TABLE flavored_things (
    thing_id  INT   PRIMARY KEY REFERENCES things (id) ON DELETE CASCADE,
    flavor    TEXT  NOT NULL
);
CREATE TABLE shaped_things (
    thing_id  INT   PRIMARY KEY REFERENCES things (id) ON DELETE CASCADE,
    shape     TEXT  NOT NULL
);
-- etc...

Writable view implementation for flavored_things

CREATE VIEW flavored_view AS
    SELECT t.*,
           f.*
      FROM things t
      JOIN flavored_things f ON f.thing_id = t.id;

CREATE FUNCTION flavored_trig () RETURNS TRIGGER AS $fun$
DECLARE
    inserted_id  INT;
BEGIN
    IF TG_OP = 'INSERT' THEN
        INSERT INTO things VALUES (  -- (A)
            DEFAULT,
            NEW.name
            -- (plus 30 more columns)
        ) RETURNING id INTO inserted_id;
        INSERT INTO flavored_things VALUES (
            inserted_id,
            NEW.flavor
        );
        RETURN NEW;
    ELSIF TG_OP = 'UPDATE' THEN
        UPDATE things SET  -- (B)
            name = NEW.name
            -- (plus 30 more columns)
        WHERE id = OLD.id;
        UPDATE flavored_things SET
            flavor = NEW.flavor
        WHERE thing_id = OLD.id;
        RETURN NEW;
    ELSIF TG_OP = 'DELETE' THEN
        DELETE FROM flavored_things WHERE thing_id = OLD.id;
        DELETE FROM things WHERE id = OLD.id;
        RETURN OLD;
    END IF;
END;
$fun$ LANGUAGE plpgsql;

CREATE TRIGGER write_flavored
    INSTEAD OF INSERT OR UPDATE OR DELETE ON flavored_view
    FOR EACH ROW EXECUTE PROCEDURE flavored_trig();

The statements marked "(A)" and "(B)" above are what I would like to replace with a call to a helper function.

Helper function for INSERT

My initial attempt was to replace statement "(A)" with

inserted_id = insert_thing(NEW);

using this function

CREATE FUNCTION insert_thing (new_thing RECORD) RETURNS INTEGER AS $fun$
DECLARE
    inserted_id  INT;
BEGIN
    INSERT INTO things (name) VALUES (
        new_thing.name
        -- (plus 30 more columns)
    ) RETURNING id INTO inserted_id;
    RETURN inserted_id;
END;
$fun$ LANGUAGE plpgsql;

This fails with the error message "PL/pgSQL functions cannot accept type record".

Giving the parameter the type things doesn't work when the function is called as insert_thing(NEW): "function insert_thing(flavored_view) does not exist".

Simple casting doesn't seem to be available here; insert_thing(NEW::things) produces "cannot cast type flavored_view to things". Writing a CAST function for each view would remove what we gained by using a helper function.

Any ideas?

like image 517
Zilk Avatar asked Jan 23 '15 12:01

Zilk


People also ask

How do I return a record in PostgreSQL?

To return a table from the function, you use RETURNS TABLE syntax and specify the columns of the table. Each column is separated by a comma (, ). In the function, we return a query that is a result of a SELECT statement.

What is := in PostgreSQL?

= is for comparison. := is for assignment.

Can we use PL SQL in PostgreSQL?

PL/pgSQL is easy to learn and simple to use. PL/pgSQL comes with PostgreSQL by default. The user-defined functions and stored procedures developed in PL/pgSQL can be used like any built-in functions and stored procedures. PL/pgSQL inherits all user-defined types, functions, and operators.

What is Setof in PostgreSQL?

Alternatively, an SQL function can be declared to return a set (that is, multiple rows) by specifying the function's return type as SETOF sometype , or equivalently by declaring it as RETURNS TABLE( columns ) . In this case all rows of the last query's result are returned. Further details appear below.


2 Answers

There are various options, depending on the complete picture.
Basically, your insert function could work like this:

CREATE FUNCTION insert_thing (_thing flavored_view)
   RETURNS int AS
$func$
   INSERT INTO things (name) VALUES ($1.name) -- plus 30 more columns
   RETURNING id;
$func$ LANGUAGE sql;

Using the row type of the view, because NEW in your trigger is of this type.
Use a simple SQL function, which can be inlined and might perform better.

Demo call:

SELECT insert_thing('(1, foo, 1, bar)');

Inside your trigger flavored_trig ():

inserted_id := insert_thing(NEW);

Or, basically rewritten:

IF TG_OP = 'INSERT' THEN
   INSERT INTO flavored_things(thing_id, flavor)
   VALUES (insert_thing(NEW), NEW.flavor);
   RETURN NEW;
ELSIF ...

record is not a valid type outside PL/pgSQL, it's just a generic placeholder for a yet unknown row type in PL/pgSQL) so you cannot use it for an input parameter in a function declaration.

For a more dynamic function accepting various row types you could use a polymorphic type. Examples:

  • How to return a table by rowtype in PL/pgSQL
  • Refactor a PL/pgSQL function to return the output of various SELECT queries
  • How to write a function that returns text or integer values?
like image 192
Erwin Brandstetter Avatar answered Oct 07 '22 05:10

Erwin Brandstetter


Basically you can convert a record to a hstore variable and pass the hstore variable instead of a record variable to a function. You convert record to hstore i.e. so:

DECLARE r record; h hstore;
h = hstore(r);

Your helper function should also be changed so:

CREATE FUNCTION insert_thing (new_thing hstore) RETURNS INTEGER AS $fun$
DECLARE
    inserted_id  INT;
BEGIN
    INSERT INTO things (name) VALUES (
        new_thing -> 'name'
        -- (plus 30 more columns)
    ) RETURNING id INTO inserted_id;
    RETURN inserted_id;
END;
$fun$ LANGUAGE plpgsql;

And the call:

inserted_id = insert_thing(hstore(NEW));

hope it helps

like image 30
Tom-db Avatar answered Oct 07 '22 05:10

Tom-db