X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id C0CE232AA3C for ; Mon, 18 Oct 2004 17:00:17 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 27038-01 for ; Mon, 18 Oct 2004 16:00:06 +0000 (GMT) Received: from ezyield.com (unknown [209.164.30.206]) by svr1.postgresql.org (Postfix) with ESMTP id 6F0B832A9F5 for ; Mon, 18 Oct 2004 17:00:02 +0100 (BST) Received: from localhost (xolinux73g2 [127.0.0.1]) by ezyield.com (8.12.5/8.11.6) with ESMTP id i9IIuWH6024466 for ; Mon, 18 Oct 2004 11:56:32 -0700 Received: from ezyield.com ([127.0.0.1]) by localhost (mail.ezyield.com [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 23968-05 for ; Mon, 18 Oct 2004 11:56:24 -0700 (PDT) Received: from KENTA (67.108.170.254.ptr.us.xo.net [67.108.170.254]) by ezyield.com (8.12.5/8.11.6) with SMTP id i9IItUn5024395 for ; Mon, 18 Oct 2004 11:55:31 -0700 From: "Kent Anderson" To: "Pgsql-Sql@Postgresql. Org" Subject: Ordering a record returned from a stored procedure Date: Mon, 18 Oct 2004 11:59:09 -0400 Message-ID: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0010_01C4B509.E0558290" X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook IMO, Build 9.0.2416 (9.0.2910.0) Importance: Normal X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441 X-Virus-Scanned: by amavisd-new 20040701 at ezyield.com X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=2.9 tagged_above=0.0 required=5.0 tests=FORGED_MUA_OIMO, HTML_50_60, HTML_MESSAGE X-Spam-Level: ** X-Archive-Number: 200410/112 X-Sequence-Number: 19215 This is a multi-part message in MIME format. ------=_NextPart_000_0010_01C4B509.E0558290 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit I am pulling a report from the database using a stored procedure but cannot get the information to return in a specific order unless I hardcode the order by clause. CREATE OR REPLACE FUNCTION submissionreport(INTEGER, DATE, TEXT) RETURNS setof submissionrec AS ' DECLARE result submissionrec%rowtype; hmhmkey ALIAS for $1; submissiondate ALIAS for $2; sort ALIAS for $3; BEGIN RAISE NOTICE ''The sort order should be: %.'', sort; FOR result IN SELECT (..... select all necessary fields ...) FROM (.... tables ...) WHERE (... contraints) ORDER BY sort LOOP RETURN next result; END LOOP; RETURN result; END; ' LANGUAGE plpgsql; What am I missing? The returned data is ordered if the "Order By" clause has the values hard coded but doesn't seem to read the "sort" variable. Any help would be appreciated. Kent Anderson ------=_NextPart_000_0010_01C4B509.E0558290 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
I am pull= ing a=20 report from the database using a stored procedure but cannot get the inform= ation=20 to return in a specific order unless I hardcode the order by clause.=20
 
CREATE OR= REPLACE=20 FUNCTION submissionreport(INTEGER, DATE, TEXT) RETURNS setof submissionrec = AS=20 '
DECLARE
          = ;     =20 result=20 submissionrec%rowtype;
        &= nbsp;      =20 hmhmkey ALIAS for=20 $1;
           &n= bsp;   =20 submissiondate ALIAS for=20 $2;
           &n= bsp;   =20 sort ALIAS for $3;
 
BEGIN
RAISE=20 NOTICE ''The sort order should be: %.'', sort;
FOR result IN
 SELECT
    (..... select all necessary fields=20 ...)
FROM      
   = (....=20 tables ...)
WHERE   
   = (...=20 contraints)
&nbs= p;
ORDER BY=20 sort
LOOP
        RETURN next=20 result;
END LOOP;
 
 RETURN=20 result;
 

END;
' LANGUAGE plpgsql;
 
What am I missing?= The=20 returned data is ordered if the "Order By" clause has the values = hard=20 coded but doesn't seem to read the "sort" variable.
&nbs= p;
Any help would be= =20 appreciated.
           =             &nb= sp;=20
 
Kent Anderson
------=_NextPart_000_0010_01C4B509.E0558290--