The following bug has been logged online: Bug reference: 4642 Logged by: Eduard Deacoon Email address: d...@yandex.ru PostgreSQL version: all Operating system: FreeBSD Description: EXECUTE work incorrect Details:
Hi! EXECUTE work 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! -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
