From: | Stefan(dot)Ardeleanu(at)siveco(dot)ro |
---|---|
To: | pgsql-novice(at)postgresql(dot)org |
Subject: | Help in dynamic execution in plpgsql |
Date: | 2005-02-21 10:31:12 |
Message-ID: | E383500F7C4E70448157685481BF824801A64F8A@internets3.main.siveco.ro |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
I have a schema schema_1 and a table named NMSAutomaticWorkstation.
I would like to run dynamically data from this table changing all the
possible clauses (select, where, order by).
The function is named NMSAutomaticWorkstation_rwc (NMSAutomaticWorkstation
read by where clause).
I have a string and I want to execute the string. Can you tell me how can I
get the proper result set (the mistakes in the body of the function or the
mistakes in the calling statement).
Thanks in advanced. Here is the code.
SET SEARCH_PATH TO schema_1;
CREATE OR REPLACE FUNCTION NMSAutomaticWorkstation_rwc (VARCHAR (500),
VARCHAR (500), VARCHAR (500)) RETURNS VOID AS $$
DECLARE
v_selectList ALIAS FOR $1;
v_whereClause ALIAS FOR $2;
v_orderByClause ALIAS FOR $3;
v_id INTEGER;
v_SQL VARCHAR (4000);
BEGIN
v_SQL := 'SELECT ' || v_selectList || ' FROM NMSAutomaticWorkstation
';
IF v_whereClause IS NOT NULL THEN
v_SQL := v_SQL || ' WHERE ' || v_whereClause;
END IF;
IF v_orderByClause IS NOT NULL THEN
v_SQL := v_SQL || ' ORDER BY ' || v_orderByClause;
END IF;
v_SQL := v_SQL || ';';
EXECUTE v_SQL;
RETURN;
END;
$$ LANGUAGE plpgsql;
select NMSAutomaticWorkstation_rwc ('*', '1=1', NULL)
Stefan Ardeleanu
From | Date | Subject | |
---|---|---|---|
Next Message | Kjetil Haaland | 2005-02-21 14:16:40 | operator class for user defined type |
Previous Message | Iain | 2005-02-21 06:42:23 | Re: PostgreSQL Errors... |