Re: choiche of function language was: Re: 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@[EMAIL PROTECTED]
)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general