[HACKERS] 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 ☭ [EMAIL PROTECTED] ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] quote_boolean() and friends missing
Markus Bertheau =?UTF-8?Q?=E2=98=AD?= [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] quote_boolean() and friends missing
В Пнд, 20/06/2005 в 10:01 -0400, Tom Lane пишет:
Markus Bertheau =?UTF-8?Q?=E2=98=AD?= [EMAIL PROTECTED] 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 ☭ [EMAIL PROTECTED]
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [HACKERS] quote_boolean() and friends missing
Markus Bertheau =?UTF-8?Q?=E2=98=AD?= [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] quote_boolean() and friends missing
В Пнд, 20/06/2005 в 10:10 -0400, Tom Lane пишет: Markus Bertheau =?UTF-8?Q?=E2=98=AD?= [EMAIL PROTECTED] 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 ☭ [EMAIL PROTECTED] ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] quote_boolean() and friends missing
On Mon, 20 Jun 2005, Tom Lane wrote: Markus Bertheau =?UTF-8?Q?=E2=98=AD?= [EMAIL PROTECTED] 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? ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] quote_boolean() and friends missing
Stephan Szabo [EMAIL PROTECTED] 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
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] quote_boolean() and friends missing
Markus Bertheau =?UTF-8?Q?=E2=98=AD?= [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
