quote_boolean() and friends missing

Markus Bertheau =?UTF-8?Q?=E2=98=AD?= <twanger@bluetwanger.de> writes:

I'm writing an application with a lot of PL/pgSQL and am constructing
SQL queries on the fly. The quote_literal() and quote_ident() functions
proved very much needed. Similarly, I need functions that return the SQL
representation of all the other datatypes, not just TEXT: quote_boolean
(), quote_date() and so on. For the sake of completeness I think these
functions should exist. While the use of to_char() resp. decode() for
some types is possible, it does feel like a workaround to me. Opinions?
Do these functions belong in the PostgreSQL core?

What exactly do you think they would do? There is no analogy to
inserting escape characters for those other datatypes.

regards, tom lane

В Пнд, 20/06/2005 в 10:01 -0400, Tom Lane пишет:

Markus Bertheau =?UTF-8?Q?=E2=98=AD?= <twanger@bluetwanger.de> writes:

I'm writing an application with a lot of PL/pgSQL and am constructing
SQL queries on the fly. The quote_literal() and quote_ident() functions
proved very much needed. Similarly, I need functions that return the SQL
representation of all the other datatypes, not just TEXT: quote_boolean
(), quote_date() and so on. For the sake of completeness I think these
functions should exist. While the use of to_char() resp. decode() for
some types is possible, it does feel like a workaround to me. Opinions?
Do these functions belong in the PostgreSQL core?

What exactly do you think they would do? There is no analogy to
inserting escape characters for those other datatypes.

They would return the SQL representation of a given value.

quote_boolean(TRUE) = 'TRUE'
quote_bytea('\377'::bytea) = '\\377' (literally \377)

Maybe quote_* is not the right name, but the functionality is needed.
I'm currently looking for a way to get the SQL representation of a bytea
value and it looks like I'm going to have to write a C function for
that.

Markus

--
Markus Bertheau ☭ <twanger@bluetwanger.de>

Markus Bertheau =?UTF-8?Q?=E2=98=AD?= <twanger@bluetwanger.de> writes:

Maybe quote_* is not the right name, but the functionality is needed.

I don't think so --- at least not in plpgsql, which can do this already.
Just assign the value to a text variable and then use quote_literal.

regards, tom lane

В Пнд, 20/06/2005 в 10:10 -0400, Tom Lane пишет:

Markus Bertheau =?UTF-8?Q?=E2=98=AD?= <twanger@bluetwanger.de> writes:

Maybe quote_* is not the right name, but the functionality is needed.

I don't think so --- at least not in plpgsql, which can do this already.
Just assign the value to a text variable and then use quote_literal.

Isn't that a workaround? Or is that the way such things are done in
plpgsql?

Markus

--
Markus Bertheau ☭ <twanger@bluetwanger.de>

On Mon, 20 Jun 2005, Tom Lane wrote:

Markus Bertheau =?UTF-8?Q?=E2=98=AD?= <twanger@bluetwanger.de> writes:

Maybe quote_* is not the right name, but the functionality is needed.

I don't think so --- at least not in plpgsql, which can do this already.
Just assign the value to a text variable and then use quote_literal.

Would that work for a bytea with embedded nul characters or does that run
the risk of terminating the value early?

Stephan Szabo <sszabo@megazone.bigpanda.com> writes:

On Mon, 20 Jun 2005, Tom Lane wrote:

I don't think so --- at least not in plpgsql, which can do this already.
Just assign the value to a text variable and then use quote_literal.

Would that work for a bytea with embedded nul characters or does that run
the risk of terminating the value early?

Works for me:

regression=# create function froob(bytea) returns text as $$
regression$# declare t text;
regression$# begin
regression$# t := $1;
regression$# return quote_literal(t);
regression$# end$$ language plpgsql;
CREATE FUNCTION
regression=# select froob('\\377\\000\\377'::bytea);
froob
-------------------
'\\377\\000\\377'
(1 row)

regression=#

regards, tom lane

Markus Bertheau =?UTF-8?Q?=E2=98=AD?= <twanger@bluetwanger.de> writes:

Just assign the value to a text variable and then use quote_literal.

Isn't that a workaround? Or is that the way such things are done in
plpgsql?

It's the way it's done --- plpgsql does this by invoking the datatype
output function and then the text input function. There has been talk
of formalizing that in other contexts by making an explicit cast to text
do that for every datatype, but nothing's been done about it yet.

regards, tom lane