BUG #4640: Drop leading zero in EXECUTE
От | Eduard Deacoon |
---|---|
Тема | BUG #4640: Drop leading zero in EXECUTE |
Дата | |
Msg-id | 200902050751.n157ptcU030590@wwwmaster.postgresql.org обсуждение исходный текст |
Ответы |
Re: BUG #4640: Drop leading zero in EXECUTE
Re: BUG #4640: Drop leading zero in EXECUTE |
Список | pgsql-bugs |
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!
В списке pgsql-bugs по дате отправления: