Skip site navigation (1) Skip section navigation (2)

BUG #4640: Drop leading zero in EXECUTE

From: "Eduard Deacoon" <deac(at)yandex(dot)ru>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #4640: Drop leading zero in EXECUTE
Date: 2009-02-05 07:51:55
Message-ID: 200902050751.n157ptcU030590@wwwmaster.postgresql.org (view raw or flat)
Thread:
Lists: pgsql-bugs
The following bug has been logged online:

Bug reference:      4640
Logged by:          Eduard Deacoon
Email address:      deac(at)yandex(dot)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!

Responses

pgsql-bugs by date

Next:From: Adrian SandorDate: 2009-02-05 08:14:57
Subject: BUG #4641: Wrong estimates for multi-column conditions
Previous:From: Dave PageDate: 2009-02-04 20:31:06
Subject: Re: BUG #4635: postgres.exe crash when RAISE NOTICE during debugging of PL/pgSQL function in PgAdminIII

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group