Dear murali,
We use psql for our ERP software .We found CREATE TYPE is useful in creating
new data type similar to creating domain.
For eg
CREATE TYPE date_condition (
condition_id int,
from_date date,
to_date date);
Instead , you can CREATE TABLE (ORDERSID INTEGER,ORDERDATE
TIMESTAMP,COMPANYNAME TEXT,EVENTNAME CHARACTER VARYING);
You can have result returning a set of records
Feel free to comment on it
Regrds
sathish
On Tue, Aug 12, 2008 at 3:08 PM, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
> Hi,
>
> Iam new to Postgresql, now i need to create a Dynamic SQL Query for
> returning the record set
> based on my Input Parameters. I looked up some of the documents and worked
> out some more ...
>
> MY Postgresql Version In Local: 7.4
> MY Postgresql Version In Development: 8.2
>
>
> -- DROP TYPE ORDERREPORT;
> CREATE TYPE ORDERREPORT AS (ORDERSID INTEGER,ORDERDATE
> TIMESTAMP,COMPANYNAME TEXT,EVENTNAME CHARACTER VARYING);
>
>
> -- DROP FUNCTION FUN_ORDERREPORT(IN pmOrderId INTEGER,IN pmCompanyId
> INTEGER,IN pmEventId INTEGER);
> CREATE OR REPLACE FUNCTION FUN_ORDERREPORT(IN pmOrderId INTEGER,IN
> pmCompanyId INTEGER,IN pmEventId INTEGER) RETURNS SETOF ORDERREPORT AS
> '
> DECLARE
> vResult ORDERREPORT%ROWTYPE;
> vSql TEXT = ''
> SELECT
> ORDR.ORDERSID AS OrderID,
> ORDR.INITIATED AS Order_Date,
> COMP.COMPANYNAME AS Company_Name,
> EVNT.EVENTNAME AS Event_Name
> FROM
> ORDERS ORDR
> INNER JOIN COMPANY COMP ON COMP.COMPANYID = ORDR.COMPANY
> INNER JOIN EVENT EVNT ON EVNT.COMPANY = COMP.COMPANYID
> WHERE
> ORDR.EVENT = EVNT.EVENTID '';
> BEGIN
> IF $1 IS NOT NULL THEN
> vSql = vSql ||'' AND ORDR.ORDERSID = ''|| $1;
> END IF;
>
> IF $2 IS NOT NULL THEN
> vSql = vSql ||'' AND COMP.COMPANYID = ''|| $2;
> END IF;
>
> IF $3 IS NOT NULL THEN
> vSql = vSql ||'' AND EVNT.EVENTID = ''|| $3;
> END IF;
> EXECUTE vSql INTO vResult;
> RETURN NEXT vResult;
> END '
> LANGUAGE 'PLPGSQL';
>
> Result:
>
> events=# SELECT * FROM FUN_ORDERREPORT(102881,NULL,NULL);
> ordersid | orderdate | companyname | eventname
> ----------+----------------------------+-------------+-----------------
> 102881 | 2006-02-10 14:49:53.002653 | PhotoMania | Photos
> (1 row)
>
> events=# SELECT * FROM FUN_ORDERREPORT(NULL,NULL,NULL);
> ordersid | orderdate | companyname | eventname
> ----------+----------------------------+-------------+-----------------
> 102881 | 2006-02-10 14:49:53.002653 | PhotoMania | Photos
> (1 row)
>
>
> Iam getting just the first record from the recordset.
> Can someone help me how can i return all query results from a Dynamic SQL
> Query?
>
>
> --
> Thanks,
> MuraliDharan V
>
