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
