Thread: quote_boolean() and friends missing
Hi, 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? -- Markus Bertheau ☭ <twanger@bluetwanger.de>
Markus Bertheau ☭ <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 ☭ <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 ☭ <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 ☭ <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 ☭ <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 ☭ <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