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.)
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...
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.
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?
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.
= is for comparison. := is for assignment.
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.
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.
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 ...
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.record
For a more dynamic function accepting various row types you could use a polymorphic type. Examples:
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With