Re: BUG #4640: Drop leading zero in EXECUTE

On Thu, 5 Feb 2009, Eduard Deacoon wrote:

> For example:
> --- Function convert column to string with delimiter
> --- $1 - TABLE with COLUMN to convert
> --- $2 - COLUMN to convert
> --- $3 - COLUMN for WHERE CLAUSE
> --- $4 - WHERE value
> --- $5 - delimeter
> --- In fact: SELECT $2 FROM $1 WHERE $3 = $4 returning as string with
> delimiter $5
> CREATE OR REPLACE FUNCTION column_to_string(TEXT, TEXT, TEXT, TEXT, TEXT)
>         RETURNS TEXT AS
> E'
> DECLARE
>         string_res TEXT := NULL;
>         r RECORD;
> BEGIN
>         FOR r IN EXECUTE ''SELECT '' || QUOTE_IDENT($2) || '' AS t '' || ''
>                 FROM '' || QUOTE_IDENT($1) || ''
>                         WHERE '' || QUOTE_IDENT($3) || '' = '' ||
> $4||''::TEXT'' ||
>                             '' ORDER BY '' || QUOTE_IDENT($2) || '' ASC''

The output of that is going to look something like
 SELECT "Valueof$2" AS t FROM "Valueof$1" WHERE "Valueof$3" =
 Valueof$4::text ORDER BY "Valueof$2" ASC.

So, given say a call with ('a', 'b', 'c', '003', ',') you'll get
 SELECT "b" AS t FROM "a" WHERE "c" = 003::text ORDER BY "b" ASC

In that case, when executed the 003 is going to be treated as a number
(and thus is the same as 3). If you wanted the 003 to be treated as a
string literal in the string to be executed, you need to quote it,
preferably with quote_literal.