Unanswered: Dynamic sql, how can I view the query results
I have 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 (show me the mistakes in the body of the function or the mistakes in the calling statement).
Thanks in advanced. Here is the code.
CREATE OR REPLACE FUNCTION NMSAutomaticWorkstation_rwc (VARCHAR (500), VARCHAR (500), VARCHAR (500)) RETURNS VOID AS $$
v_selectList ALIAS FOR $1;
v_whereClause ALIAS FOR $2;
v_orderByClause ALIAS FOR $3;
v_SQL VARCHAR (4000);
v_SQL := 'SELECT ' || v_selectList || ' FROM NMSAutomaticWorkstation ';
IF v_whereClause IS NOT NULL THEN
v_SQL := v_SQL || ' WHERE ' || v_whereClause;
IF v_orderByClause IS NOT NULL THEN
v_SQL := v_SQL || ' ORDER BY ' || v_orderByClause;
v_SQL := v_SQL || ';';
$$ LANGUAGE plpgsql;
the call statement may look like this one:
select NMSAutomaticWorkstation_rwc ('*', '1=1', NULL)