Обсуждение: 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?
2008/5/9 tekwiz <ryoung@medicalpharmacies.com>: > 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 >
On Sat, 10 May 2008 07:35:36 +0200 "Pavel Stehule" <pavel.stehule@gmail.com> 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
Hello 2008/5/10 Ivan Sergio Borgonovo <mail@webthatworks.it>: > On Sat, 10 May 2008 07:35:36 +0200 > "Pavel Stehule" <pavel.stehule@gmail.com> 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 >
On May 10, 2008, at 12:14 AM, Ivan Sergio Borgonovo wrote: > On Sat, 10 May 2008 07:35:36 +0200 > "Pavel Stehule" <pavel.stehule@gmail.com> 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
Ivan Sergio Borgonovo <mail@webthatworks.it> 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
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
mail@webthatworks.it (Ivan Sergio Borgonovo) writes: > On Sat, 10 May 2008 07:35:36 +0200 > "Pavel Stehule" <pavel.stehule@gmail.com> 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? Well, plpgsql has the merit that its operations and control structures are directly oriented towards "database stuff," so there's no need to (for instance) invoke functions (e.g. - running queries via spi_exec(), spi_query(), ...) in order to perform database operations. One of the salutory effects is that there is a pl/pgsql debugger that can automatically handle things like single-stepping, and it does not need to be aware of any Perl/Python internals. -- "cbbrowne","@","linuxfinances.info" http://linuxfinances.info/info/sap.html "...you might as well skip the Xmas celebration completely, and instead sit in front of your linux computer playing with the all-new-and-improved linux kernel version." -- Linus Torvalds