Обсуждение: 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 andASC/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/sortingorder combination) If it's not possible, could anyone implement this feature into one of future versions? I think that it would be quite handyto have something like that. Thank you for your responses. ------------------- Menu.sk - Denne aktualizovany zoznam obedovych menu a restauracii s presnou lokalizaciou.
Вложения
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
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);
"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);
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
);
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;
$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;
| myid | myerrdesc |
| 11 | Update transactiondate |
| 9 | Update reknr |
| 15 | Update lineamountmst |
| 13 | Update invoiceid |
| 8 | Original invoice not found |
| 1 | Missing InvoiceID |
| 2 | Missing InventTransID |
| 7 | Invoice with a negative amount |
| 3 | Illegal oneshot dates (start <> end) |
| 4 | Illegal dates (start > end) |
| 6 | Creditnote with a positive amount |
| 12 | Attempt to change transactiondate |
| 10 | Attempt to change reknr |
| 16 | Attempt to change lineamountmst |
| 14 | Attempt to change invoiceid |
| 5 | Accountnumber not defined |
I hope this is useful to you.
>>> "A. Kretschmer" <andreas.kretschmer@schollglas.com> 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
---------------------------(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
>>> "A. Kretschmer" <andreas.kretschmer@schollglas.com> 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
---------------------------(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
"A. Kretschmer" <andreas.kretschmer@schollglas.com> 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 ASCWHEN 2 THEN name DESCWHEN 3 THEN height ASCWHEN 4 THEN height DESCELSE id ASCEND 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'sPostgreSQL training!
Gregory Stark <stark@enterprisedb.com> 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
"Tom Lane" <tgl@sss.pgh.pa.us> writes: > Gregory Stark <stark@enterprisedb.com> 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'sPostgreSQL training!
Simple question - but my brain is not working:
This is my table
Column | Type |
Modifiers
-----------+------------------------+-----------------------------------------------------------------id |
integer | not null default
nextval('emr_clinicschedule_id_seq'::regclass)clinic_id | integer | not nulldate | date
|day | smallint |status | boolean | not nullopen | time without time
zone|close | time without time zone |reason | character varying(64) |active | boolean | not
null
I want to find any value:
SELECT id FROM schedule WHERE clinic_id = '%s' AND date = '%s'
But I want to make sure the clinic exist first then find if the date
exist 2nd.
How do I do that?
Thanks in advance,
J
On Wed, 06 Feb 2008 16:01:09 -0500 PostgreSQL Admin <postgres@productivitymedia.com> wrote: > I want to find any value: > > SELECT id FROM schedule WHERE clinic_id = '%s' AND date = '%s' > > But I want to make sure the clinic exist first then find if the date > exist 2nd. I'm not sure what you mean by "first" and "second" but if you mean that you want a list of all clinics that match the clinic ID and you want to know of all of those which ones have the specified date then perhaps this is what you want. SELECT id, date = '%s' FROM schedule WHERE clinic_id = '%s'; -- D'Arcy J.M. Cain <darcy@druid.net> | Democracy is three wolves http://www.druid.net/darcy/ | and a sheep voting on +1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner.