SEO

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
>

--
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


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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
>

--
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


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

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