Re: execution plan and record variable in dynamic sql

Hi

This bug/feature is related to cached plans - PLpgSQL is able to invalidate
plans when some database objects are dropped, but it is not this use case.

I am not sure, if we would to change current behave, because it is safe
against to unexpected changes of database schema.

As solution of your problem, I can help only - don't use plpgsql language
for too dynamic tasks. When you dynamicly change datatypes, then use a more
code paths in function or use other PL language.

PLpgSQL is perfect language for static use cases .. fixed business logic.

Typically PLPerl or PLPython is good language (preferable language) for
these tasks.

Regards

Pavel

2014-12-16 10:14 GMT+01:00 Пушкин Сергей <pushkinsv(at)gmail(dot)com>:
>
> I'm not sure how Line 8 is calculated in your example...
>>
>
> "Line 8" refers to line 12 "raise notice 'r.status %', r.status;"
> (I have added comments into function body later, thus changing line
> numbering, sorry for this mistake)
>
> You may wish to share a real use case that you think requires this to not
>> error - and explain what it is you think it should do instead.
>>
>
> The real use case is a bit more complex wrapper function, which executes
> arbitrary sql code, which may (or may not) return textual status code.
> The function itself intended to always return this textual code as output
> parameter "status" or null if there was no output from executed code.
> (the same for int8 parameter "forward")
>
> create or replace function logic.execute_trigger(sql text, process int8,
> params anyarray)
> returns table (status text, forward int8) language plpgsql volatile as $_$
> declare
> r record;
> status text;
> forward int8;
> begin
> begin
> execute sql using process, params into r;
> exception
> when syntax_error then execute sql using process, params;
> end;
>
> begin
> status=r.status;
> exception
> when undefined_column then status=null;
> end;
> begin forward=r.forward; exception when undefined_column then
> forward=null; end;
> return query select status, forward;
> end;
> $_$;
>
> After implementing this (as a part of quite complicated logic-level
> triggering system)
> I found that if this function is called inside one session, and executed
> sql
> were like this: (1) "select 'text 1' as status;" and this: (2) "select
> format('text 2') as status"
> I get "ERROR: type of parameter 13 (text) does not match that when
> preparing the plan (unknown)"
> because (1) returns field "status" of type 'unknown' and (2) of 'text'.
>
> However, if the same sql in the same order were executed in different
> consquenting
> sessions, there was no error, and that was what I expected.
>
> explain what it is you think it should do instead
>>
>
> I think the fields of record variable should remain accessible regardless
> of
> type of its values (consider we sure that field with certain name do exists
> in this record), and should be castable to another type explicitly or
> implicitly
>
> also consider the following examle, which do the same thing (executes the
> same
> dynamic sql in the same order in one transaction), but does not lead to
> any error:
>
> do $_$
> declare
> r record;
> begin
> execute $$ select 1 as status $$ into r;
> raise notice '%', r;
> raise notice '%', r.status;
> execute $$ select 'test' as status $$ into r;
> raise notice '%', r;
> raise notice '%', r.status;
> end;
> $_$;
>
> I think that test case stated in my initial letter should have the same
> behaviour.
>
> --
> Serge Pushkin
>
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>