[SQL] UNICODE and PL/PGSQL
Dear all, This works: SELECT '\x65'; => it returns the letter 'e'. When I do the following in PL/PGSQL it returns the same letter 'e' (as might be expected); CREATE OR REPLACE FUNCTION "public"."myfunction" (out result varchar) RETURNS varchar AS $body$ DECLARE charset varchar := ''; BEGIN charset := charset || '\x65'; result := charset; RETURN; END; $body$ LANGUAGE 'plpgsql' IMMUTABLE RETURNS NULL ON NULL INPUT SECURITY INVOKER; However, when I compose charset dynamically it doesn't work anymore. Following function returns 'x65' instead of 'e'. Can anyone tell me why that is and how to make it work? The target is of course to change the values in the FOR control structure. CREATE OR REPLACE FUNCTION "public"."myfunction" (out result varchar) RETURNS varchar AS $body$ DECLARE charset varchar := ''; BEGIN FOR i IN 101..101 LOOP charset := charset || '\x' || to_hex(i); END LOOP; result := charset; RETURN; END; $body$ LANGUAGE 'plpgsql' IMMUTABLE RETURNS NULL ON NULL INPUT SECURITY INVOKER; Thanks for any help. Bart
Re: [SQL] PgAgent [where is it?]
Hi, Ezequias, Ezequias Rodrigues da Rocha wrote: > I need to create some jobs on my database but I can't find out the > pgagent (executable). Can someone help me in this challenger ? At least on debian, pgagent has its own package, and is compiled out of the pgadmin3-sources. HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] UNICODE and PL/PGSQL
On mán, 2006-11-27 at 11:52 +0100, Bart Degryse wrote: > Dear all, > This works: SELECT '\x65'; => it returns the letter 'e'. yes, but: test=# select '\x'||'65'; ?column? -- x65 (1 row) > When I do the following in PL/PGSQL > FOR i IN 101..101 LOOP > charset := charset || '\x' || to_hex(i); > END LOOP; gnari ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] UNICODE and PL/PGSQL
Hi gnari, I suppose your statement test=# select '\x'||'65'; is done on some command line interface. I don't have that. I can only use some client program. I'm using EMS SQL Manager 2007 and pgAdmin III 1.3 None of them accepts your statement. My point is that in my first version of the function ( charset := charset || '\x65'; ) a unicode hex value for the letter e is stored in charset. When I try to do the same for a range of hex values ( FOR i IN 101..101 LOOPcharset := charset || '\x' || to_hex(i); ) it is not longer a bunch of hex values that get stored but a series of varchars. When you run the first version of the function ( SELECT myfunction(); ) 'e' is returned, with the second version 'x65' is returned, while I want also 'e' to be returned. Any ideas? >>> Ragnar <[EMAIL PROTECTED]> 2006-11-27 15:37 >>> On mán, 2006-11-27 at 11:52 +0100, Bart Degryse wrote: > Dear all, > This works: SELECT '\x65'; => it returns the letter 'e'. yes, but: test=# select '\x'||'65'; ?column? -- x65 (1 row) > When I do the following in PL/PGSQL > FOR i IN 101..101 LOOP > charset := charset || '\x' || to_hex(i); > END LOOP; gnari
Re: [SQL] UNICODE and PL/PGSQL
Hi, Bart, Bart Degryse wrote: > I suppose your statement > test=# select '\x'||'65'; > is done on some command line interface. I don't have that. I can only > use some client program. All versions of PostgreSQL I know are shipped with "psql" as command line interface. (It's a client program, actually. :-) > I'm using EMS SQL Manager 2007 and pgAdmin III 1.3 > None of them accepts your statement. I just tried with pgAdmin III 1.4.3, and it worked fine. > When I try to do the same for a range of hex values ( FOR i IN 101..101 > LOOPcharset := charset || '\x' || to_hex(i); ) it is not longer a > bunch of hex values that get stored but a series of varchars. The problem is that the \x escaping is done in the parser, so in your first function, the query engine actually sees "charset := charset || 'e';" In the second function, the '\x' string is parsed as is, and converted to the String 'x' instead of being rejected as broken \x sequence, I think for compatibility reasons. Then, the engine sees: "charset := charset || 'x' || to_hex(i);" Maybe you can change it to (ASCII version): "charset := charset || chr(i);" or (256-bit version): "charset := charset || decode(to_hex(i),'hex'); HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Auto-Response: Re: [SQL] UNICODE and PL/PGSQL
This is an automated response. Your email... From:[EMAIL PROTECTED] Subject: Re: [SQL] UNICODE and PL/PGSQL has been successfully received and I will reply as soon as possible. Thank you. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] UNICODE and PL/PGSQL
Hi Markus, I should have said "The ICT department doesn't give me access to the psql command line interface". Thanks for explaining what goes wrong. I now understand the problem. It doesn't solve it though. I haven't tried your first suggestion since ASCII won't be good enough. I also need to be able to do something like charset := charset || '\xC2\xA9'; but then in the dynamic form like charset := charset || '\xC2\x' || to_hex(i)'; So I thought your second suggestion might be of help. I get an error though: ERROR: operator does not exist: character varying || bytea HINT: No operator matches the given name and argument type(s). You may need to add explicit type casts. CONTEXT: SQL statement "SELECT $1 || decode(to_hex( $2 ), 'hex')" >>> Markus Schaber <[EMAIL PROTECTED]> 2006-11-27 16:16 >>> Hi, Bart, Bart Degryse wrote: > I suppose your statement > test=# select '\x'||'65'; > is done on some command line interface. I don't have that. I can only > use some client program. All versions of PostgreSQL I know are shipped with "psql" as command line interface. (It's a client program, actually. :-) > I'm using EMS SQL Manager 2007 and pgAdmin III 1.3 > None of them accepts your statement. I just tried with pgAdmin III 1.4.3, and it worked fine. > When I try to do the same for a range of hex values ( FOR i IN 101..101 > LOOPcharset := charset || '\x' || to_hex(i); ) it is not longer a > bunch of hex values that get stored but a series of varchars. The problem is that the \x escaping is done in the parser, so in your first function, the query engine actually sees "charset := charset || 'e';" In the second function, the '\x' string is parsed as is, and converted to the String 'x' instead of being rejected as broken \x sequence, I think for compatibility reasons. Then, the engine sees: "charset := charset || 'x' || to_hex(i);" Maybe you can change it to (ASCII version): "charset := charset || chr(i);" or (256-bit version): "charset := charset || decode(to_hex(i),'hex'); HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org
Re: [SQL] UNICODE and PL/PGSQL
On mán, 2006-11-27 at 15:54 +0100, Bart Degryse wrote: > Hi gnari, > I suppose your statement > test=# select '\x'||'65'; > is done on some command line interface. I don't have that. I can only > use some client program. I'm using EMS SQL Manager 2007 and pgAdmin > III 1.3 > None of them accepts your statement. > My point is that in my first version of the function ( charset := > charset || '\x65'; ) a unicode hex value for the letter e is stored > in charset. my point was that '\x65' as a literal is read as 'e', but '\\x' || '65' is just the concatation of 2 2-char varchars > When I try to do the same for a range of hex values ( FOR i IN > 101..101 LOOPcharset := charset || '\x' || to_hex(i); ) you might be able to do what you want with set_byte() and encode(). CREATE OR REPLACE FUNCTION myfunction() RETURNS varchar AS $x$ DECLARE x bytea := ' '; BEGIN FOR i in 101..105 LOOP x:=set_byte(x,i-101,i); END LOOP; RETURN encode(x,''escape''); END; $x$ LANGUAGE 'plpgsql'; select myfunction(); gnari ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] UNICODE and PL/PGSQL
Bart Degryse wrote: Hi Markus, I should have said "The ICT department doesn't give me access to the psql command line interface". Thanks for explaining what goes wrong. I now understand the problem. It doesn't solve it though. What's wrong with the chr() function? http://www.postgresql.org/docs/8.1/static/functions-string.html -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] UNICODE and PL/PGSQL
Hi, Bart, Bart Degryse wrote: > I should have said "The ICT department doesn't give me access to the > psql command line interface". Hmm, and you can't install psql on the same host you use to run pgadmin? It's just a client, that connects to PostgreSQL the same way as pgamdin. > ERROR: operator does not exist: character varying || bytea > HINT: No operator matches the given name and argument type(s). You may > need to add explicit type casts. > CONTEXT: SQL statement "SELECT $1 || decode(to_hex( $2 ), 'hex')" Hmm. Strange. Does it happen in a plpgsql function, or when issuing the select as PREPARE statement via pgadmin? Which PostgreSQL version are you running? Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 1: 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
Auto-Response: Re: [SQL] UNICODE and PL/PGSQL
This is an automated response. Your email... From:[EMAIL PROTECTED] Subject: Re: [SQL] UNICODE and PL/PGSQL has been successfully received and I will reply as soon as possible. Thank you. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] UNICODE and PL/PGSQL
As the manual says : Character with the given ASCII code I need characters outside the ASCII range. Something like charset := charset || '\xC2\x' || to_hex(i)'; >>> Richard Huxton 2006-11-27 16:53 >>> Bart Degryse wrote: > Hi Markus, > I should have said "The ICT department doesn't give me access to the psql > command line interface". > Thanks for explaining what goes wrong. I now understand the problem. It > doesn't solve it though. What's wrong with the chr() function? http://www.postgresql.org/docs/8.1/static/functions-string.html -- Richard Huxton Archonet Ltd
Re: [SQL] UNICODE and PL/PGSQL
I haven't got enough permissions to install new programs. Big brother... ;-) I get the error in a plpgsql function I use PostgreSQL 8.1.4 with EMS SQL Manager 2005 (3.6.0.1). >>> Markus Schaber <[EMAIL PROTECTED]> 2006-11-27 16:53 >>> Hi, Bart, Bart Degryse wrote: > I should have said "The ICT department doesn't give me access to the > psql command line interface". Hmm, and you can't install psql on the same host you use to run pgadmin? It's just a client, that connects to PostgreSQL the same way as pgamdin. > ERROR: operator does not exist: character varying || bytea > HINT: No operator matches the given name and argument type(s). You may > need to add explicit type casts. > CONTEXT: SQL statement "SELECT $1 || decode(to_hex( $2 ), 'hex')" Hmm. Strange. Does it happen in a plpgsql function, or when issuing the select as PREPARE statement via pgadmin? Which PostgreSQL version are you running? Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org
Re: [SQL] UNICODE and PL/PGSQL
Bart Degryse wrote: As the manual says : Character with the given ASCII code I need characters outside the ASCII range. Something like charset := charset || '\xC2\x' || to_hex(i)'; Well, if you've tried it for characters > 127 and it didn't work then I don't know that it is possible at all. You *did* try the function? -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] UNICODE and PL/PGSQL
Hi, Bart, Bart Degryse wrote: > I haven't got enough permissions to install new programs. Big brother... ;-) A developer doesn't get the permissions to use the tools he need for his work? Developers absolutely _must_ have root / admin permissions on their own workstations, that's a prerequirement for them to fulfil their job. Time to search a new employer, honest! > I get the error in a plpgsql function Ah, I just saw that the "decode" function returns bytea, not text. I'll need a further look into this... Sorry, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 1: 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: [SQL] select into
Hi Adrian,
Thanks very much for your help...it is a pity you can't do this in plpgsql coz
i have almost every thing else I need, and I hate to use yet another language.
What does it take to add this mechanism to the language? Is any one planning to
add it? It can come it handy I bet.
Thanks again,
Mustafa...
Adrian Klaver <[EMAIL PROTECTED]> wrote: On Sunday 26 November 2006 02:45 pm,
Adrian Klaver wrote:
>
> I am afraid I can't make it work either.
I could not make it work with pl/pgsql, but I did manage to come up with a
solution using pl/pythonu.
The function is as follows-
CREATE OR REPLACE FUNCTION dat_col_py(text) RETURNS text AS
$Body$
tbl_name=args[0]
cols=plpy.prepare("select column_name from information_schema.columns where\
table_name=$1",["text"])
clean=plpy.prepare("delete from dat_col where table_name=$1",["text"])
clean_tbl=plpy.execute(clean,[tbl_name])
ins=plpy.prepare("insert into dat_col values($1,$2,$3),["text","text","text"])
data_rs=plpy.execute('select * from '+tbl_name)
cols_rs=plpy.execute(cols,[tbl_name])
for i in range(len(data_rs)):
for j in range(len(cols_rs)):
plpy.execute(ins,(tbl_name,cols_rs[j]['column_name'],
data_rs[i][cols_rs[j]['column_name'] ]))
$Body$
LANGUAGE plpythonu;
For this to work I created a table dat_col(table_name text,column_name
text,column_data text). The function deletes old data from the table before
it is run, based on table name. Just run as dat_col_py("table name"). This
assumes you have pl/pythonu installed.
--
Adrian Klaver
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
-
Cheap Talk? Check out Yahoo! Messenger's low PC-to-Phone call rates.
Re: [SQL] select into
On Monday 27 November 2006 06:31 pm, Mulham freshcode wrote: > Hi Adrian, > > Thanks very much for your help...it is a pity you can't do this in plpgsql > coz i have almost every thing else I need, and I hate to use yet another > language. What does it take to add this mechanism to the language? Is any > one planning to add it? It can come it handy I bet. > > Thanks again, > > Mustafa... > It may not be a shortcoming of the language, but a shortcoming in how well I understand it. I haven't given up hope I can make it work in plpgsql. The problem was I was going in circles. -- Adrian Klaver [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: explain analyze is your friend
