Re: choiche of function language was: Re: [GENERAL] dynamic procedure call
I wrote: > We've poked a few loopholes in the strong typing over the years > --- the whole business of EXECUTE versus direct evaluation of a > query can be seen as allowing weak typing for EXECUTE'd queries. > But it's still the language's design center. Rereading that, it suddenly struck me that Pavel's recent addition of USING to EXECUTE provides a klugy way to get at a run-time-determined member of a row variable, which seems to be the single most-requested facility in this area. I put together the following test case, which tries to print out the values of fields selected by trigger arguments: create or replace function foo() returns trigger as $$ declare r record; begin for i in 1 .. tg_argv[0] loop execute 'select $1 . ' || tg_argv[i] || ' as x' into r using NEW; raise notice '% = %', tg_argv[i], r.x; end loop; return new; end $$ language plpgsql; create table tab(f1 int, f2 text, f3 timestamptz); create trigger footrig before insert on tab for each row execute procedure foo (3,f1,f2,f3); insert into tab values(42, 'foo', now()); (BTW, in this example it's truly annoying that TG_ARGV[] isn't a "real" array that you can use array_lower/array_upper on. Maybe that is worth fixing sometime.) Unfortunately this doesn't quite work, because plpgsql is resolutely strongly typed: NOTICE: f1 = 42 ERROR: type of "r.x" does not match that when preparing the plan CONTEXT: PL/pgSQL function "foo" line 6 at RAISE IOW, it gets through the first cycle of the loop okay, but in the second one the "r.x" subexpression has already been planned on the expectation that r.x is of type int. You can get around this if you are willing to coerce all possible results to the same type, eg text: create or replace function foo() returns trigger as $$ declare t text; begin for i in 1 .. tg_argv[0] loop execute 'select cast ( $1 . ' || tg_argv[i] || ' as text)' into t using new; raise notice '% = %', tg_argv[i], t; end loop; return new; end $$ language plpgsql; et voila: NOTICE: f1 = 42 NOTICE: f2 = foo NOTICE: f3 = 2008-05-10 11:38:33.677035-04 So, it's a hack, and it relies on a feature that won't be out till 8.4, but it *is* possible ... regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: choiche of function language was: Re: [GENERAL] dynamic procedure call
Ivan Sergio Borgonovo <[EMAIL PROTECTED]> writes: > It is clear what is at least one of the advantage of plpython or > plperl over plpgsql, but then what are the advantages of plpgsql over > the rest of the crowd other than resembling the language used in > Oracle? Others made some other comments already, but I think the issue that is specifically concerning you is the question of strong vs weak typing. plpgsql is designed as a strongly typed language, meaning that the types of all objects are supposed to be predetermined and not changing. This makes it difficult if not impossible to write stuff that can refer to run-time-selected columns. But you get benefits in terms of better error checking and improved performance --- a weakly typed language could never cache any plans, not even for trivial expressions. We've poked a few loopholes in the strong typing over the years --- the whole business of EXECUTE versus direct evaluation of a query can be seen as allowing weak typing for EXECUTE'd queries. But it's still the language's design center. I think it'd be possible to build a weakly typed language that was just as well integrated with SQL as plpgsql is, but it would likely be markedly slower in use. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: choiche of function language was: Re: [GENERAL] dynamic procedure call
On May 10, 2008, at 12:14 AM, Ivan Sergio Borgonovo wrote: On Sat, 10 May 2008 07:35:36 +0200 "Pavel Stehule" <[EMAIL PROTECTED]> wrote: your application different execution paths. Generally I can say, so plpgsql isn't well language for this games, and better is using plperl, plpython or other external language. It is clear what is at least one of the advantage of plpython or plperl over plpgsql, but then what are the advantages of plpgsql over the rest of the crowd other than resembling the language used in Oracle? A much better impedance match to the database. It's designed for doing database-ish things. The biggest advantage there is that your datatypes are the database datatypes and your expression parser is the sql expression parser. That makes using things like timestamp or interval or custom database types simpler and cleaner from pl/pgsql than from, say, pl/perl/ Cheers, Steve -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: choiche of function language was: Re: [GENERAL] dynamic procedure call
Hello 2008/5/10 Ivan Sergio Borgonovo <[EMAIL PROTECTED]>: > On Sat, 10 May 2008 07:35:36 +0200 > "Pavel Stehule" <[EMAIL PROTECTED]> wrote: > >> your application different execution paths. Generally I can say, so >> plpgsql isn't well language for this games, and better is using >> plperl, plpython or other external language. > > It is clear what is at least one of the advantage of plpython or > plperl over plpgsql, but then what are the advantages of plpgsql over > the rest of the crowd other than resembling the language used in > Oracle? > SQL integration and compatibility with PostgreSQL. PL/pgSQL uses PostgreSQL expression evaluation - so all PostgreSQL functions are simply accessible from plpgsql. Next - plpgsql variables are compatible (are same) with PostgreSQL internal datatypes - so you don't need any conversion between Postgres and plpgsql. plpgsql is best glue of SQL statements. Pavel > -- > Ivan Sergio Borgonovo > http://www.webthatworks.it > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
choiche of function language was: Re: [GENERAL] dynamic procedure call
On Sat, 10 May 2008 07:35:36 +0200 "Pavel Stehule" <[EMAIL PROTECTED]> wrote: > your application different execution paths. Generally I can say, so > plpgsql isn't well language for this games, and better is using > plperl, plpython or other external language. It is clear what is at least one of the advantage of plpython or plperl over plpgsql, but then what are the advantages of plpgsql over the rest of the crowd other than resembling the language used in Oracle? -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] dynamic procedure call
2008/5/9 tekwiz <[EMAIL PROTECTED]>: > How do I code PL/pgSQL to select a procedure name from a table and > then execute it from within another procedure and pass that procedure > a particular ROWTYPE and return a ROWTYPE? you can use EXECUTE statement. But you have to be careful when these functions will returns different types. Then you have to have in your application different execution paths. Generally I can say, so plpgsql isn't well language for this games, and better is using plperl, plpython or other external language. Pavel > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] dynamic procedure call
How do I code PL/pgSQL to select a procedure name from a table and then execute it from within another procedure and pass that procedure a particular ROWTYPE and return a ROWTYPE? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
