Re: execution plan and record variable in dynamic sql

On Tue, Dec 16, 2014 at 2:15 AM, =D0=9F=D1=83=D1=88=D0=BA=D0=B8=D0=BD =D0=
=A1=D0=B5=D1=80=D0=B3=D0=B5=D0=B9 [via PostgreSQL] <
ml-node+s1045698n5830862h46@n5.nabble.com> wrote:

> > I'm not sure how Line 8 is calculated in your example...
>
> 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.
>
>
Fair enough but the difference here is that you have dedicated raise
statements for each of the two record structure outcomes whereas in the
complaint you are using the same statement for both.

The question here is whether there is some way for the placeholder argument
in the raise notice to always be considered "text" and for all assigned
values to be implicitly converted to text at runtime in an
unchecked/uncached manner.  Since all types have textual conversion/output
functions, and the idea of "notice" is to output text, that is indeed what
is expected.

I'm not sure this really solves your use case though.  I am pretty certain
you are going to be stuck trying to enforce that input queries use
homogenious types by using explicit casting.  SELECT 1::text; SELECT
'test'::text;

David J.




--
View this message in context: http://postgresql.nabble.com/execution-plan-a=
nd-record-variable-in-dynamic-sql-tp5830846p5830927.html
Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.