Re: Allow SQL/plpgsql functions to accept record - Mailing list pgsql-hackers
| From | David G. Johnston |
|---|---|
| Subject | Re: Allow SQL/plpgsql functions to accept record |
| Date | |
| Msg-id | CAKFQuwY4n0iBgs62eaTL6Nhd_6FcgJ2AFPKRPPQ8j-sMWCE3Gw@mail.gmail.com Whole thread Raw |
| In response to | Allow SQL/plpgsql functions to accept record (Jim Nasby <Jim.Nasby@BlueTreble.com>) |
| Responses |
Re: Allow SQL/plpgsql functions to accept record
|
| List | pgsql-hackers |
Is there a fundamental reason SQL/plpgsql functions won't accept record as an input type? If not, can someone point me at a patch that might show how much work would be involved in adding support?
My particular use case is a generic function that will count how many fields in a record are NULL. I can do it in pure SQL (below), but was hoping to wrap the entire thing in a function. Right now, I have to add a call to row_to_json() to the function call.
SELECT count(*)
FROM json_each_text( row_to_json($1) ) a
WHERE value IS NULL
See also:
SELECT (src.v).* FROM ( VALUES (ROW(1,2,3)) ) src (v);
ERROR: record type has not been registered
While it may not be necessary to solve both problems I suspect they have the same underlying root cause - specifically the separation of concerns between the planner and the executor.
ISTM that the planner needs to be able to create arbitrarily named composite types and leave them "registered" in the session somewhere for the executor to find. Session because:
PREPARE prep_rec AS SELECT record_input_func(v) FROM ( VALUES (ROW($1::integer,$2::boolean,$3::text)) src (v);
EXECUTE prep_rec USING (1, true, 'hi!');
If it requires additional smarts in the executor to make this all work I suspect the cost-benefit equations end up supporting the somewhat more verbose but workable status-quo.
I'm not sure how { row_to_json(record) } works but SQL (including pl/pgsql) needs to have some source of definition for what the record type should be in reality - and that source currently is the catalogs whose rows are locked by the planner and injected, I think, into a session cache. The source query in pl/pgsql defines the type for fully embedded use of the record placeholder while the caller's function alias provides that information for RETURNS record. The calling query needs to provide the same information for "CREATE FUNCTION func( arg1 record )" since the body of the pl/pgsql function needs to instantiate "arg1" with a known type as soon as the function is entered. It is theoretically possible to impute the needed anonymous type from the query definition - the problem is how and where to register that information for execution.
At least for pl/pgsql I could see possibly doing something like "func( arg1 packed_record_bytes)" and having pl/pgsql understand how to unpack those bytes into an anonymous but structured record (like it would with SELECT ... INTO record_var) seems plausible. I would not expect pl/SQL to allow anything of the sort as it doesn't seem compatible with the idea of inline-ability.
Maybe the "C" code for "row_to_json" (or libpq in general) can provide inspiration (particularly for the "pack/unpack bytes") but as I do not know "C" I'm going to have to leave that to others.
David J.
pgsql-hackers by date: