Re: [HACKERS] Composite Types and Function Parameters
On 10/28/2010 12:23 PM, David E. Wheeler wrote: On Oct 27, 2010, at 9:08 PM, Andrew Dunstan wrote: Well, it turns out that the hashref required exactly one more line to achieve. We already have all the infrastructure on the composite handling code, and all it requires it to enable it for the RECORDOID case. I don't suppose that it would be just as easy to allow an array passed to PL/Perl to be read into the PL/Perl function as an array reference, would it? Since it would break backcompat, it would need to be enabled by a plperl.* directive, but man, I would kill for that. Of course it's possible, but it's a different feature. As for just as easy, no, it's much more work. I agree it should be done, though. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Composite Types and Function Parameters
On Oct 27, 2010, at 9:08 PM, Andrew Dunstan wrote: Well, it turns out that the hashref required exactly one more line to achieve. We already have all the infrastructure on the composite handling code, and all it requires it to enable it for the RECORDOID case. I don't suppose that it would be just as easy to allow an array passed to PL/Perl to be read into the PL/Perl function as an array reference, would it? Since it would break backcompat, it would need to be enabled by a plperl.* directive, but man, I would kill for that. Best, David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Composite Types and Function Parameters
On Oct 28, 2010, at 9:31 AM, Andrew Dunstan wrote: Of course it's possible, but it's a different feature. As for just as easy, no, it's much more work. I agree it should be done, though. I bet we could raise some money to fund it's development. How much work are we talking about here? Best, David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Composite Types and Function Parameters
On 10/25/2010 09:32 PM, Andrew Dunstan wrote: On 10/25/2010 07:12 PM, Tom Lane wrote: However, that objection doesn't hold for plperl or pltcl (and likely not plpython, though I don't know that language enough to be sure). So it would be a reasonable feature request to teach those PLs to accept record parameters. I think the fact that they don't stems mostly from nobody having revisited their design since the infrastructure that supports record_out was created. That seems like a good idea. I'll look at it for plperl. A naive implementation turns out to be really trivial. It's about two lines, and we can then do: andrew=# create function rfunc (x record) returns text language plperlu as $$ use Data::Dumper; return Dumper(shift); $$; CREATE FUNCTION andrew=# select rfunc(row(c.relname,n.nspname)) from pg_class c join pg_namespace n on c.relnamespace = n.oid limit 1; rfunc -- $VAR1 = '(pg_statistic,pg_catalog)';+ But I think we can do better than this. We should really pass an hashref with the record's column names as keys rather than just calling record_out. I'll work on that. cheers andrew
Re: [HACKERS] Composite Types and Function Parameters
On 10/25/2010 09:32 PM, Andrew Dunstan wrote: On 10/25/2010 07:12 PM, Tom Lane wrote: However, that objection doesn't hold for plperl or pltcl (and likely not plpython, though I don't know that language enough to be sure). So it would be a reasonable feature request to teach those PLs to accept record parameters. I think the fact that they don't stems mostly from nobody having revisited their design since the infrastructure that supports record_out was created. That seems like a good idea. I'll look at it for plperl. A naive implementation turns out to be really trivial. It's about two lines, and we can then do: andrew=# create function rfunc (x record) returns text language plperlu as $$ use Data::Dumper; return Dumper(shift); $$; CREATE FUNCTION andrew=# select rfunc(row(c.relname,n.nspname)) from pg_class c join pg_namespace n on c.relnamespace = n.oid limit 1; rfunc -- $VAR1 = '(pg_statistic,pg_catalog)';+ But I think we can do better than this. We should really pass an hashref with the record's column names as keys rather than just calling record_out. I'll work on that. cheers andrew
Re: [HACKERS] Composite Types and Function Parameters
Andrew Dunstan and...@dunslane.net writes: But I think we can do better than this. We should really pass an hashref with the record's column names as keys rather than just calling record_out. I'll work on that. Definitely. If you aren't providing that info then it's hard to write a generic function, which is more or less the whole point here. I'd even argue that it'd be nice if the function could find out the data types of the record's columns; though I have no idea what a reasonable API for that would be in Perl. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Composite Types and Function Parameters
On 10/27/2010 11:38 PM, Tom Lane wrote: Andrew Dunstanand...@dunslane.net writes: But I think we can do better than this. We should really pass an hashref with the record's column names as keys rather than just calling record_out. I'll work on that. Definitely. If you aren't providing that info then it's hard to write a generic function, which is more or less the whole point here. I'd even argue that it'd be nice if the function could find out the data types of the record's columns; though I have no idea what a reasonable API for that would be in Perl. Well, it turns out that the hashref required exactly one more line to achieve. We already have all the infrastructure on the composite handling code, and all it requires it to enable it for the RECORDOID case. As for your idea of exposing type info, we could certainly do that using the same mechanism we use for the trigger $_TD stuff. Patch so far attached. cheers andrew *** a/src/pl/plperl/plperl.c --- b/src/pl/plperl/plperl.c *** *** 1371,1377 plperl_validator(PG_FUNCTION_ARGS) argtypes, argnames, argmodes); for (i = 0; i numargs; i++) { ! if (get_typtype(argtypes[i]) == TYPTYPE_PSEUDO) ereport(ERROR, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), errmsg(PL/Perl functions cannot accept type %s, --- 1371,1378 argtypes, argnames, argmodes); for (i = 0; i numargs; i++) { ! if (get_typtype(argtypes[i]) == TYPTYPE_PSEUDO ! argtypes[i] != RECORDOID) ereport(ERROR, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), errmsg(PL/Perl functions cannot accept type %s, *** *** 2105,2111 compile_plperl_function(Oid fn_oid, bool is_trigger) typeStruct = (Form_pg_type) GETSTRUCT(typeTup); /* Disallow pseudotype argument */ ! if (typeStruct-typtype == TYPTYPE_PSEUDO) { free(prodesc-proname); free(prodesc); --- 2106,2113 typeStruct = (Form_pg_type) GETSTRUCT(typeTup); /* Disallow pseudotype argument */ ! if (typeStruct-typtype == TYPTYPE_PSEUDO ! procStruct-proargtypes.values[i] != RECORDOID) { free(prodesc-proname); free(prodesc); *** *** 2115,2121 compile_plperl_function(Oid fn_oid, bool is_trigger) format_type_be(procStruct-proargtypes.values[i]; } ! if (typeStruct-typtype == TYPTYPE_COMPOSITE) prodesc-arg_is_rowtype[i] = true; else { --- 2117,2124 format_type_be(procStruct-proargtypes.values[i]; } ! if (typeStruct-typtype == TYPTYPE_COMPOSITE || ! procStruct-proargtypes.values[i] == RECORDOID) prodesc-arg_is_rowtype[i] = true; else { -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Composite Types and Function Parameters
Hi Merlin, I completely forgot about hstore! I'll give it a go. Thanks! From: Merlin Moncure mmonc...@gmail.com To: Greg grigo...@yahoo.co.uk Cc: Pavel Stehule pavel.steh...@gmail.com; pgsql-hackers@postgresql.org Sent: Mon, 25 October, 2010 23:52:55 Subject: Re: [HACKERS] Composite Types and Function Parameters On Mon, Oct 25, 2010 at 6:38 PM, Greg grigo...@yahoo.co.uk wrote: Hi Pavel, thanks! Yeah, thats what I though. I have to have a custom type or a very ugly looking solution for passing the params then. To Postgre dev. team: If anyone who involved in Postgre development reading this, just a feature suggestion: allow array that can accept combination of any data types to be passed to a function, for example: // declare create function TEST ( anytypearray[] ) ... // calling perform TEST (array[bool, int, etc.] ) This would make such a nice adition to the development for postgre. Although this may be complecated to achieve. probably hstore would be more appropriate for something like that. You can also declare functions taking composite arrays, anyarray, variadic array, and variadic any, although the latter requires function implementation in C to get the most out of it. merlin
[HACKERS] Composite Types and Function Parameters
Hi guys, got across an interesting problem of passing params to a function in postgre: is it possible to pass a composite parameter to a function without declaring a type first? For example: // declare a function create function TEST ( object??? ) object???.paramName // using parameter // calling perform TEST( ROW(string, int, bool, etc...) ) Or do I have to do the type declaration for that parameter? Thanks!
Re: [HACKERS] Composite Types and Function Parameters
Hello I am thinking, so it isn't possible. There are a general datatype anyelement, but it cannot accept a second general type record. CREATE TYPE p AS (a text, b int, c bool); CREATE OR REPLACE FUNCTION fp(p) RETURNS int AS $$ BEGIN RAISE NOTICE 'a = %', $1.a; RETURN $1.b; END; $$ LANGUAGE plpgsql; postgres=# select fp(row('hello',10, false)); NOTICE: a = hello fp 10 (1 row) Regards Pavel Stehule Time: 0.814 ms postgres=# select fp(row('hello',10, false)); 2010/10/25 Greg grigo...@yahoo.co.uk Hi guys, got across an interesting problem of passing params to a function in postgre: is it possible to pass a composite parameter to a function without declaring a type first? For example: // declare a function create function TEST ( object??? ) object???.paramName// using parameter // calling perform TEST( ROW(string, int, bool, etc...) ) Or do I have to do the type declaration for that parameter? Thanks!
Re: [HACKERS] Composite Types and Function Parameters
Hi Pavel, thanks! Yeah, thats what I though. I have to have a custom type or a very ugly looking solution for passing the params then. To Postgre dev. team: If anyone who involved in Postgre development reading this, just a feature suggestion: allow array that can accept combination of any data types to be passed to a function, for example: // declare create function TEST ( anytypearray[] ) ... // calling perform TEST (array[bool, int, etc.] ) This would make such a nice adition to the development for postgre. Although this may be complecated to achieve. Thanks! From: Pavel Stehule pavel.steh...@gmail.com To: Greg grigo...@yahoo.co.uk Cc: pgsql-hackers@postgresql.org Sent: Mon, 25 October, 2010 17:46:47 Subject: Re: [HACKERS] Composite Types and Function Parameters Hello I am thinking, so it isn't possible. There are a general datatype anyelement, but it cannot accept a second general type record. CREATE TYPE p AS (a text, b int, c bool); CREATE OR REPLACE FUNCTION fp(p) RETURNS int AS $$ BEGIN RAISE NOTICE 'a = %', $1.a; RETURN $1.b; END; $$ LANGUAGE plpgsql; postgres=# select fp(row('hello',10, false)); NOTICE: a = hello fp 10 (1 row) Regards Pavel Stehule Time: 0.814 ms postgres=# select fp(row('hello',10, false)); 2010/10/25 Greg grigo...@yahoo.co.uk Hi guys, got across an interesting problem of passing params to a function in postgre: is it possible to pass a composite parameter to a function without declaring a type first? For example: // declare a function create function TEST ( object??? ) object???.paramName// using parameter // calling perform TEST( ROW(string, int, bool, etc...) ) Or do I have to do the type declaration for that parameter? Thanks!
Re: [HACKERS] Composite Types and Function Parameters
On Mon, Oct 25, 2010 at 6:38 PM, Greg grigo...@yahoo.co.uk wrote: Hi Pavel, thanks! Yeah, thats what I though. I have to have a custom type or a very ugly looking solution for passing the params then. To Postgre dev. team: If anyone who involved in Postgre development reading this, just a feature suggestion: allow array that can accept combination of any data types to be passed to a function, for example: // declare create function TEST ( anytypearray[] ) ... // calling perform TEST (array[bool, int, etc.] ) This would make such a nice adition to the development for postgre. Although this may be complecated to achieve. probably hstore would be more appropriate for something like that. You can also declare functions taking composite arrays, anyarray, variadic array, and variadic any, although the latter requires function implementation in C to get the most out of it. merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Composite Types and Function Parameters
Merlin Moncure mmonc...@gmail.com writes: probably hstore would be more appropriate for something like that. An array is certainly completely the wrong thing if you don't intend all the items to be the same datatype... You can also declare functions taking composite arrays, anyarray, variadic array, and variadic any, although the latter requires function implementation in C to get the most out of it. If you're willing to write in C you can already create functions that accept type record (see record_out for an existence proof/coding example). Making plpgsql do that would be problematic though: it's not so much the record parameter itself, as that you'd be excessively restricted in what you can do with it. If the column names and datatypes aren't pretty predictable, plpgsql isn't going to be the language you want to work in. However, that objection doesn't hold for plperl or pltcl (and likely not plpython, though I don't know that language enough to be sure). So it would be a reasonable feature request to teach those PLs to accept record parameters. I think the fact that they don't stems mostly from nobody having revisited their design since the infrastructure that supports record_out was created. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Composite Types and Function Parameters
On Oct 25, 2010, at 4:12 PM, Tom Lane wrote: However, that objection doesn't hold for plperl or pltcl (and likely not plpython, though I don't know that language enough to be sure). So it would be a reasonable feature request to teach those PLs to accept record parameters. I think the fact that they don't stems mostly from nobody having revisited their design since the infrastructure that supports record_out was created. +1 # Would love to see that. David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Composite Types and Function Parameters
On 10/25/2010 07:12 PM, Tom Lane wrote: However, that objection doesn't hold for plperl or pltcl (and likely not plpython, though I don't know that language enough to be sure). So it would be a reasonable feature request to teach those PLs to accept record parameters. I think the fact that they don't stems mostly from nobody having revisited their design since the infrastructure that supports record_out was created. That seems like a good idea. I'll look at it for plperl. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers