Thread: BUG #12690: EXECUTE statement fails with dynamic column name on record variable

The following bug has been logged on the website:

Bug reference:      12690
Logged by:          Patrick
Email address:      patrick@vanlaake.net
PostgreSQL version: 9.3.5
Operating system:   Ubuntu server 12.04
Description:

A recent question on StackOverflow was answered with a PL/PgSQL function
that includes a dynamic statement built from a row-typed record variable

(http://stackoverflow.com/questions/28164457/how-to-use-pl-pgsql-to-transform-an-original-table-to-desired-two-tables-in-post/28166069#28166069).
When running the function, the statement:

EXECUTE 'SELECT th.' || quote_ident(period) INTO times;

generates the run-time error:

ERROR:  missing FROM-clause entry for table "th"
LINE 1: SELECT th."1996-04"
               ^
QUERY:  SELECT th."1996-04"
CONTEXT:  PL/pgSQL function normalize_things_happened() line 22 at EXECUTE
statement
********** Error **********

ERROR: missing FROM-clause entry for table "th"
SQL state: 42P01
Context: PL/pgSQL function normalize_things_happened() line 22 at EXECUTE
statement

where the local variable th is a row-typed record, period is a string
representing a column name and times is an integer.

When converting the statement to the non-dynamic corresponding version
SELECT th."1996-04" INTO times; the statement executes without an error.

In this specific context the parser seems to not support or disallow the use
of a record variable. Is this a bug ?

On 2015-01-28 11:18, patrick@vanlaake.net wrote:
> EXECUTE 'SELECT th.' || quote_ident(period) INTO times;
>
> In this specific context the parser seems to not support or disallow the use
> of a record variable. Is this a bug ?

No.  It has nothing to do with the type of the variable; PL/PgSQL
variables are not visible to the query being EXECUTEd at all.  See
http://www.postgresql.org/docs/9.4/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN,
specifically the part:

"No substitution of PL/pgSQL variables is done on the computed command
string. Any required variable values must be inserted in the command
string as it is constructed; or you can use parameters as described below."


.m