pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSql ORDER BY and ASC/DESC question
6+ messages / 5 participants
[nested] [flat]
* Sql ORDER BY and ASC/DESC question
@ 2008-01-30 10:35 Jaroslav Sivy <yarex@pobox.sk>
0 siblings, 1 reply; 6+ messages in thread
From: Jaroslav Sivy @ 2008-01-30 10:35 UTC (permalink / raw)
To: pgsql-sql
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.
Attachments:
attachment.txt (text/plain, 47B) download
Put the text of the welcome message here.
* Re: Sql ORDER BY and ASC/DESC question
@ 2008-01-30 10:42 A. Kretschmer <andreas.kretschmer@schollglas.com>
parent: Jaroslav Sivy <yarex@pobox.sk>
0 siblings, 2 replies; 6+ messages in thread
From: A. Kretschmer @ 2008-01-30 10:42 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Sql ORDER BY and ASC/DESC question
@ 2008-01-30 12:30 Bart Degryse <Bart.Degryse@indicator.be>
parent: A. Kretschmer <andreas.kretschmer@schollglas.com>
1 sibling, 0 replies; 6+ messages in thread
From: Bart Degryse @ 2008-01-30 12:30 UTC (permalink / raw)
To: pgsql-sql
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" <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 ( 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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Sql ORDER BY and ASC/DESC question
@ 2008-01-30 13:32 Gregory Stark <stark@enterprisedb.com>
parent: A. Kretschmer <andreas.kretschmer@schollglas.com>
1 sibling, 1 reply; 6+ messages in thread
From: Gregory Stark @ 2008-01-30 13:32 UTC (permalink / raw)
To: A. Kretschmer <andreas.kretschmer@schollglas.com>; +Cc: pgsql-sql
"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 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!
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Sql ORDER BY and ASC/DESC question
@ 2008-01-30 18:00 Tom Lane <tgl@sss.pgh.pa.us>
parent: Gregory Stark <stark@enterprisedb.com>
0 siblings, 1 reply; 6+ messages in thread
From: Tom Lane @ 2008-01-30 18:00 UTC (permalink / raw)
To: Gregory Stark <stark@enterprisedb.com>; +Cc: A. Kretschmer <andreas.kretschmer@schollglas.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Sql ORDER BY and ASC/DESC question
@ 2008-01-30 20:24 Gregory Stark <stark@enterprisedb.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 6+ messages in thread
From: Gregory Stark @ 2008-01-30 20:24 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: A. Kretschmer <andreas.kretschmer@schollglas.com>; pgsql-sql
"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's PostgreSQL training!
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2008-01-30 20:24 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-01-30 10:35 Sql ORDER BY and ASC/DESC question Jaroslav Sivy <yarex@pobox.sk>
2008-01-30 10:42 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
2008-01-30 12:30 ` Bart Degryse <Bart.Degryse@indicator.be>
2008-01-30 13:32 ` Gregory Stark <stark@enterprisedb.com>
2008-01-30 18:00 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-01-30 20:24 ` Gregory Stark <stark@enterprisedb.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox