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$ (view raw, whole thread or download thread mbox)
Lists: pgsql-general
According to section of

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

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

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;

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,

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-2017 The PostgreSQL Global Development Group