Re: [SQL] Sql ORDER BY and ASC/DESC question
am Wed, dem 30.01.2008, um 11:35:51 +0100 mailte Jaroslav Sivy folgendes: > Hello everyone, > > I have following problem: am using pl/sql functions to trigger some > sql code and i need to pass ORDER_BY column name and ASC/DESC sorting > order as an input parameters into that function and order the result > based on these input parameters. > > The problem is, that the only way is to create query as "string" and > then execute it as "string". Right. > > Is there any other way around how to avoid that "string query"? No. > If it's not possible, could anyone implement this feature into one of > future versions? I think that it would be quite handy to have Unlikely... Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(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
[SQL] Sql ORDER BY and ASC/DESC question
Hello everyone, I have following problem: am using pl/sql functions to trigger some sql code and i need to pass ORDER_BY column name and ASC/DESC sorting order as an input parameters into that function and order the result based on these input parameters. The problem is, that the only way is to create query as "string" and then execute it as "string". Is there any other way around how to avoid that "string query"? (i don't want to use IFs either, because 5 columns x 2 sorting orders would require 10 IFfed queries with different column/sorting order combination) If it's not possible, could anyone implement this feature into one of future versions? I think that it would be quite handy to have something like that. Thank you for your responses. --- Menu.sk - Denne aktualizovany zoznam obedovych menu a restauracii s presnou lokalizaciou. Put the text of the welcome message here. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Sql ORDER BY and ASC/DESC question
Actually there might be assuming your function is a set returning function.
This example eg works perfectly and sorts the output of the function without
having to use execute.
CREATE TABLE "public"."error_types" (
"id" SERIAL,
"errdesc" TEXT NOT NULL,
"autofix" BOOLEAN DEFAULT false NOT NULL,
CONSTRAINT "error_types_errdesc_key" UNIQUE("errdesc"),
CONSTRAINT "error_types_pkey" PRIMARY KEY("id")
) WITH (fillfactor = 100, OIDS = FALSE);
INSERT INTO "public"."error_types" ("errdesc", "autofix") VALUES ('Missing
InvoiceID', True);
INSERT INTO "public"."error_types" ("errdesc", "autofix") VALUES ('Missing
InventTransID', True);
INSERT INTO "public"."error_types" ("errdesc", "autofix") VALUES ('Illegal
oneshot dates (start <> end)', False);
INSERT INTO "public"."error_types" ("errdesc", "autofix") VALUES ('Illegal
dates (start > end)', False);
INSERT INTO "public"."error_types" ("errdesc", "autofix") VALUES
('Accountnumber not defined', False);
INSERT INTO "public"."error_types" ("errdesc", "autofix") VALUES ('Creditnote
with a positive amount', False);
INSERT INTO "public"."error_types" ("errdesc", "autofix") VALUES ('Invoice with
a negative amount', False);
INSERT INTO "public"."error_types" ("errdesc", "autofix") VALUES ('Original
invoice not found', False);
INSERT INTO "public"."error_types" ("errdesc", "autofix") VALUES ('Update
reknr', True);
INSERT INTO "public"."error_types" ("errdesc", "autofix") VALUES ('Attempt to
change reknr', False);
INSERT INTO "public"."error_types" ("errdesc", "autofix") VALUES ('Update
transactiondate', True);
INSERT INTO "public"."error_types" ("errdesc", "autofix") VALUES ('Attempt to
change transactiondate', False);
INSERT INTO "public"."error_types" ("errdesc", "autofix") VALUES ('Update
invoiceid', True);
INSERT INTO "public"."error_types" ("errdesc", "autofix") VALUES ('Attempt to
change invoiceid', False);
INSERT INTO "public"."error_types" ("errdesc", "autofix") VALUES ('Update
lineamountmst', True);
INSERT INTO "public"."error_types" ("errdesc", "autofix") VALUES ('Attempt to
change lineamountmst', False);
CREATE TYPE test3_type AS (
myid integer,
myerrdesc text
);
CREATE OR REPLACE FUNCTION test3 () RETURNS SETOF test3_type AS
$body$
DECLARE
rec test3_type;
BEGIN
FOR rec IN (
SELECT id, errdesc
FROM error_types)
LOOP
RETURN NEXT rec;
END LOOP;
RETURN;
END;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
select * from test3() order by myerrdesc desc;
myidmyerrdesc
11Update transactiondate
9Update reknr
15Update lineamountmst
13Update invoiceid
8Original invoice not found
1Missing InvoiceID
2Missing InventTransID
7Invoice with a negative amount
3Illegal oneshot dates (start <> end)
4Illegal dates (start > end)
6Creditnote with a positive amount
12Attempt to change transactiondate
10Attempt to change reknr
16Attempt to change lineamountmst
14Attempt to change invoiceid
5Accountnumber not defined
I hope this is useful to you.
>>> "A. Kretschmer" <[EMAIL PROTECTED]> 2008-01-30 11:42 >>>
am Wed, dem 30.01.2008, um 11:35:51 +0100 mailte Jaroslav Sivy folgendes:
> Hello everyone,
>
> I have following problem: am using pl/sql functions to trigger some
> sql code and i need to pass ORDER_BY column name and ASC/DESC sorting
> order as an input parameters into that function and order the result
> based on these input parameters.
>
> The problem is, that the only way is to create query as "string" and
> then execute it as "string".
Right.
>
> Is there any other way around how to avoid that "string query"?
No.
> If it's not possible, could anyone implement this feature into one of
> future versions? I think that it would be quite handy to have
Unlikely...
Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net (
http://wwwkeys.de.pgp.net/ )
---(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] Sql ORDER BY and ASC/DESC question
"A. Kretschmer" <[EMAIL PROTECTED]> writes: > am Wed, dem 30.01.2008, um 11:35:51 +0100 mailte Jaroslav Sivy folgendes: >> Hello everyone, >> >> I have following problem: am using pl/sql functions to trigger some >> sql code and i need to pass ORDER_BY column name and ASC/DESC sorting >> order as an input parameters into that function and order the result >> based on these input parameters. >> >> The problem is, that the only way is to create query as "string" and >> then execute it as "string". > > Right. >> >> Is there any other way around how to avoid that "string query"? If you're not concerned with the planner being able to find indexes to satisfy these orderings (ie, you don't mind always doing a sort) you could do something like: ORDER BY CASE ? WHEN 1 THEN name ASC WHEN 2 THEN name DESC WHEN 3 THEN height ASC WHEN 4 THEN height DESC ELSE id ASC END But to the planner this will be basically an opaque expression. It won't be able to use any indexes on these columns. Also, incidentally you might want to use text strings instead of integer labels. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training! ---(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] Sql ORDER BY and ASC/DESC question
Gregory Stark <[EMAIL PROTECTED]> writes: > If you're not concerned with the planner being able to find indexes to satisfy > these orderings (ie, you don't mind always doing a sort) you could do > something like: > ORDER BY > CASE ? > WHEN 1 THEN name ASC > WHEN 2 THEN name DESC > WHEN 3 THEN height ASC > WHEN 4 THEN height DESC > ELSE id ASC > END Uh, no, putting the ASC/DESC decoration inside a CASE like that is not gonna work --- it's only allowed at the top level of an ORDER BY clause. For numerical sort keys you can cheat by using "-x" in place of "x DESC", but I'm not aware of any equivalent hack for text keys. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Sql ORDER BY and ASC/DESC question
"Tom Lane" <[EMAIL PROTECTED]> writes: > Gregory Stark <[EMAIL PROTECTED]> writes: > >> ORDER BY >> CASE ? >> WHEN 1 THEN name ASC > > Uh, no, putting the ASC/DESC decoration inside a CASE like that is not > gonna work doh! I had a feeling something was wrong but couldn't put my finger on it before I hit send. Sigh. > For numerical sort keys you can cheat by using "-x" in place of > "x DESC", but I'm not aware of any equivalent hack for text keys. Yeah, you could do a really kludgy thing with a second sort expression where you null out one expression or the other depending on the parameter but it starts to look more and more spaghetti-like. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training! ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] Problem with the to_timestamp function
So the start_time is a varchar column, which contains a date. opal_app=# select job_id,start_time from job_status where job_id='app1201551799779' ; job_id |start_time --+-- app1201551799779 | 1 28, 2008 12:23:19 午後 (1 row) opal_app=# select job_id,to_timestamp(start_time, 'M DD, HH12:MI:SS 午後') from job_status where job_id='app1201551799779'; job_id | to_timestamp --+ app1201551799779 | 2008-01-28 02:23:19-08 (1 row) opal_app=# select job_id,to_timestamp(start_time, 'M DD, HH12:MI:SS') from job_status where job_id='app1201551799779'; job_id | to_timestamp --+ app1201551799779 | 2008-01-28 02:23:19-08 (1 row) opal_app=# select job_id,to_timestamp(start_time, 'M DD, HH:MI:SS') from job_status where job_id='app1201551799779'; job_id | to_timestamp --+ app1201551799779 | 2008-01-28 02:23:19-08 (1 row) It seems that the to_timestamp does not work properly in this case, when it comes to parsing the hours. I verified that this problem happen no matter what the input hours is and the result is always 00:min:sec or 02:min:sec. opal_app=# select job_id,start_time from job_status where job_id='app1201563668439'; job_id | start_time --+- app1201563668439 | 1 28, 2008 3:41:08 午後 (1 row) opal_app=# select job_id, to_timestamp(start_time, 'M DD, HH24:MI:SS') from job_status where job_id='app1201563668439'; job_id | to_timestamp --+ app1201563668439 | 2008-01-28 00:01:08-08 (1 row) Or opal_app=# select job_id,start_time from job_status where job_id='app1201565220760'; job_id | start_time --+- app1201565220760 | 1 28, 2008 4:07:00 午後 (1 row) opal_app=# select job_id,to_timestamp(start_time, 'M DD, HH:MI:SS') from job_status where job_id='app1201565220760'; job_id | to_timestamp --+ app1201565220760 | 2008-01-28 00:07:00-08 (1 row) Is this a bug or am I doing something wrong? Thank you for any help, Luca ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Problem with the to_timestamp function
Luca Clementi <[EMAIL PROTECTED]> writes: > It seems that the to_timestamp does not work properly in this case, > when it comes to parsing the hours. to_timestamp() is not very robust if the input doesn't exactly match what it expects for the format string. I'm not sure if that's the issue here, but have you tried just casting the string to timestamp? That would use the standard timestamp input converter, which is pretty flexible. (BTW, there is definitely 0 hope of recognizing a timezone name that's written in Chinese characters, unless maybe you fool around with the timezone-abbreviations configuration file.) regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Problem with the to_timestamp function
On Jan 30, 2008, at 19:26 , Tom Lane wrote: (BTW, there is definitely 0 hope of recognizing a timezone name that's written in Chinese characters, unless maybe you fool around with the timezone-abbreviations configuration file.) Just FYI, the characters in the original email are the equivalent of PM, rather than designating a time zone. Not that that affects your advice, of course. Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Problem with the to_timestamp function
Tom Lane wrote: Luca Clementi <[EMAIL PROTECTED]> writes: It seems that the to_timestamp does not work properly in this case, when it comes to parsing the hours. to_timestamp() is not very robust if the input doesn't exactly match what it expects for the format string. I'm not sure if that's the issue here, but have you tried just casting the string to timestamp? That would use the standard timestamp input converter, which is pretty flexible. (BTW, there is definitely 0 hope of recognizing a timezone name that's written in Chinese characters, unless maybe you fool around with the timezone-abbreviations configuration file.) I found the problem!! to_timestamp(start_time, 'M DD, HH12:MI:SS') ^^ in the formatting string I have two spaces, while in the original there is only one: 1 28, 2008 12:23:19 午後 So if I use: 'M DD, HH12:MI:SS' as a formatting string it works! I wander how come if the formatting string doesn't match properly you get such a bad behavior, would it better just a error? PS: some e-mail clients do not render properly the spacing in my first e-mail. Thanks again for your help!! Luca ---(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] Problem with the to_timestamp function
Luca Clementi <[EMAIL PROTECTED]> writes: > I found the problem!! > to_timestamp(start_time, 'M DD, HH12:MI:SS') > in the formatting string I have two spaces, while in the original there > is only one: > 1 28, 2008 12:23:19 åå¾ > So if I use: 'M DD, HH12:MI:SS' as a formatting string it works! > I wander how come if the formatting string doesn't match properly you > get such a bad behavior, would it better just a error? Yeah, it should either do something reasonable or throw an error. Improving to_timestamp's handling of such cases is on the TODO list. In the meantime, though, the standard input converter is a whole lot more flexible for slightly-out-of-spec input ... so I ask again, do you really need to_timestamp at all? regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
