Re: execution plan and record variable in dynamic sql - Mailing list pgsql-bugs
From | Пушкин Сергей |
---|---|
Subject | Re: execution plan and record variable in dynamic sql |
Date | |
Msg-id | op.xqyemrbpgskoan@pushkinsv.ood.local Whole thread Raw |
In response to | Re: execution plan and record variable in dynamic sql (David G Johnston <david.g.johnston@gmail.com>) |
Responses |
Re: execution plan and record variable in dynamic sql
Re: execution plan and record variable in dynamic sql |
List | pgsql-bugs |
> 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_errorthen 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 (iso-8859-1)" because (1) returns field "status" of type 'iso-8859-1' 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; beginexecute $$ 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
pgsql-bugs by date: