Обсуждение: BUG #4640: Drop leading zero in EXECUTE

The following bug has been logged online:

Bug reference:      4640
Logged by:          Eduard Deacoon
Email address:      deac@yandex.ru
PostgreSQL version: all
Operating system:   FreeBSD
Description:        Drop leading zero in EXECUTE
Details:

Hi!
EXECUTE has incorrect if one or more string have leading zero.

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''
        LOOP
                IF r.t IS NOT NULL AND string_res IS NULL THEN
                        string_res := r.t;
                ELSEIF r.t IS NOT NULL AND string_res IS NOT NULL THEN
                        string_res := string_res || $5 || r.t;
                END IF;
        END LOOP;
        RETURN string_res;
END;
' LANGUAGE 'plpgsql' STABLE RETURNS NULL ON NULL INPUT SECURITY INVOKER;

If $4 has leading zero it does`n work :(
Context replace with ''0 || '' || $4 work only if $4 have one leading zero.
''00 || '' || $4 does`n work, ''0 || 0 ||'' || $4 - work

Nice day!

"Eduard Deacoon" <deac@yandex.ru> writes:

> If $4 has leading zero it does`n work :(

please expand on "doesn't work"

> Context replace with ''0 || '' || $4 work only if $4 have one leading zero.
> ''00 || '' || $4 does`n work, ''0 || 0 ||'' || $4 - work


I'm sorry I'm not following this part. What parameters did you call this
function with? What did you expect to happen? What actually happened?

--
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com
  Ask me about EnterpriseDB's 24x7 Postgres support!

05.02.09, 17:16, "Gregory Stark" <stark@enterprisedb.com>:

> "Eduard Deacoon" <deac@yandex.ru> writes:
> > If $4 has leading zero it does`n work :(
> please expand on "doesn't work"
> > Context replace with ''0 || '' || $4 work only if $4 have one leading zero.
> > ''00 || '' || $4 does`n work, ''0 || 0 ||'' || $4 - work
> I'm sorry I'm not following this part. What parameters did you call this
> function with? What did you expect to happen? What actually happened?
> --
>   Gregory Stark
>   EnterpriseDB          http://www.enterprisedb.com
>   Ask me about EnterpriseDB's 24x7 Postgres support!

--

When $4 contain '0001' then have '1'
When $4 contain '0000' then have NULL
When string contain digits only with leading zero, then EXECUTE drop leading zero.

Best regards!

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.