How to pass a record to a PL/pgSQL function?
Solution 1
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:
- 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?
Solution 2
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
Zilk
Updated on June 09, 2022Comments
-
Zilk almost 2 years
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 athings
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 asinsert_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?