Skip site navigation (1) Skip section navigation (2)

Dynamic Queries in pl/pgsql [w/variable substitution]

From: "Dan Wilson" <phpPgAdmin(at)acucore(dot)com>
To: <pgsql-general(at)postgresql(dot)org>
Subject: Dynamic Queries in pl/pgsql [w/variable substitution]
Date: 2001-04-30 16:45:03
Message-ID: 000701c0d194$e7d1b860$523987cf@corp.peoplesoft.com (view raw or flat)
Thread:
Lists: pgsql-general
According to section 24.2.5.3 of
http://www.postgresql.org/users-lounge/docs/7.1/programmer/plpgsql-descripti
on.html

----------------
    FOR referrer_keys IN select * from cs_referrer_keys order by try_order
LOOP
        a_output := a_output || '' if v_'' || referrer_keys.kind || '' like
''''''''''
                 || referrer_keys.key_string || '''''''''' then return
''''''
                 || referrer_keys.referrer_type || ''''''; end if;'';
    END LOOP;

    -- This works because we are not substituting any variables
    -- Otherwise it would fail. Look at PERFORM for another way to run
functions
----------------

It says it does not work when substituting variables.  This is what I need
to do:

 full_sql := 'SELECT ' || sql_fields || ' AS full_name FROM ' || sql_table
|| ' WHERE ' || sql_id_field || ' = ' || user_id;

 FOR sql_results IN full_sql LOOP
  users_name := sql_results.full_name;
 END LOOP;

The variables sql_fields, sql_table, sql_id_field and user_id are previously
set according to some values passed into the function.

Is there a workaround that will accomplish what I am looking for, or am I
stuck to not using a function and doing a two separate queries?

Thanks in advance,
-Dan


pgsql-general by date

Next:From: Tom LaneDate: 2001-04-30 17:59:47
Subject: Re: do functions cache views?
Previous:From: Tom LaneDate: 2001-04-30 16:41:58
Subject: Re: PostgreSQL and mySQL database size question

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group