Help in dynamic execution in plpgsql

Поиск
Список
Период
Сортировка
От Stefan.Ardeleanu@siveco.ro
Тема Help in dynamic execution in plpgsql
Дата
Msg-id E383500F7C4E70448157685481BF824801A64F8A@internets3.main.siveco.ro
обсуждение исходный текст
Список 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

В списке pgsql-novice по дате отправления:

Предыдущее
От: John Napiorkowski
Дата:
Сообщение: Support for RETURNING xxx INTO yyy for INSERT and UPDATE?
Следующее
От: Jason Earl
Дата:
Сообщение: Re: Bad Boolean external represenation *HELP*!