Hi,
 
Was wondering if there was anything akin to an evaluate statement in Postgresql for dynamic strings?
 
Alex







Hi,
 
Was wondering if there was anything akin to an evaluate statement in Postgresql for dynamic strings?
 
Alex
Sun, 4 Jan 2004 13:09:02 -0800 (PST)
A E <cooljoint@yahoo.com>
Anything akin to an Evaluate Statement in Postgresql?
> Was wondering if there was anything akin to an evaluate statement in 
> Postgresql for dynamic strings?

By dint of tricky programming you can a function that can generate and 
execute arbitrary strings.  I believe there's even an example of this in 
the docs.

Chris

Mon, 05 Jan 2004 09:09:16 +0800
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Re: Anything akin to an Evaluate Statement in Postgresql?
Thanks. I searched for it and I found something. It tells me to use the perl module. But Tom Lane mentions using the execute command see(http://archives.postgresql.org/pgsql-general/2001-03/msg01614.php). 
 
Since I have no interest in picking up yet another language, I tried this:
 
   qry := ''select * from ''|| trim(realname) ||'' where ''|| trim(searchfield) ||'' like ''''%''|| trim(searchvalue) ||''%'''''';
   arrayval := string_to_array(coltoparammatch(3, talias, insertparams, insertdelimiter), '','');   
   for objectdefinition in execute qry loop
    for i in array_lower(arrayval, 1)..array_upper(arrayval, 1) loop
      qry := ''select into aliasvalue objectdefinition.''|| arrayval[i];
      execute qry;
      RAISE NOTICE ''field = %'', aliasvalue;
    end loop; 
   end loop;
 
I tried to execute a dynamic sql string using the dynamic record column name but I getting this error: ERROR:  syntax error at or near "into" at character 8. Does the execute statement not allow the into keyword into or am I not quoting right?
 
TIA
 
Alex

Christopher Kings-Lynne <chriskl@familyhealth.com.au> wrote:
> Was wondering if there was anything akin to an evaluate statement in 
> Postgresql for dynamic strings?

By dint of tricky programming you can a function that can generate and 
execute arbitrary strings. I believe there's even an example of this in 
the docs.

Chris
Thanks. I searched for it and I found something. It tells me to use the perl module. But Tom Lane mentions using the execute command see(http://archives.postgresql.org/pgsql-general/2001-03/msg01614.php). 
 
Since I have no interest in picking up yet another language, I tried this:
 
   qry := ''select * from ''|| trim(realname) ||'' where ''|| trim(searchfield) ||'' like ''''%''|| trim(searchvalue) ||''%'''''';   arrayval := string_to_array(coltoparammatch(3, talias, insertparams, insertdelimiter), '','');      for objectdefinition in execute qry loop    for i in array_lower(arrayval, 1)..array_upper(arrayval, 1) loop      qry := ''select into aliasvalue objectdefinition.''|| arrayval[i];      execute qry;      RAISE NOTICE ''field = %'', aliasvalue;    end loop;    end loop;
 
I tried to execute a dynamic sql string using the dynamic record column name but I getting this error: ERROR:  syntax error at or near "into" at character 8. Does the execute statement not allow the into keyword into or am I not quoting right?
 
TIA
 
AlexChristopher Kings-Lynne <chriskl@familyhealth.com.au> wrote:
> Was wondering if there was anything akin to an evaluate statement in > Postgresql for dynamic strings?By dint of tricky programming you can a function that can generate and execute arbitrary strings. I believe there's even an example of this in the docs.Chris
Sun, 4 Jan 2004 19:26:23 -0800 (PST)
A E <cooljoint@yahoo.com>
Re: Anything akin to an Evaluate Statement in Postgresql?
A E <cooljoint@yahoo.com> writes:
> I tried to execute a dynamic sql string using the dynamic record
> column name but I getting this error: ERROR: syntax error at or near
> "into" at character 8. Does the execute statement not allow the into
> keyword

It does not :-(.  The best way of getting data back from an EXECUTE'd
select is to use a FOR ... IN EXECUTE loop.  See the docs.

			regards, tom lane

Mon, 05 Jan 2004 01:10:30 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Anything akin to an Evaluate Statement in Postgresql? 
Tom and Chris,

I tried to use a for in loop to execute the statement to try to get some dynamic functionality but I get "ERROR:  missing ".." at end of SQL expression" what am I doing wrong?
 
Code:
 
   qry := ''select * from ''|| trim(realname) ||'' where ''|| trim(searchfield) ||'' like ''''%''|| trim(searchvalue) ||''%'''''';
   arrayval := string_to_array(coltoparammatch(3, talias, insertparams, insertdelimiter), '','');   
   for objectdefinition in execute qry loop
    for i in array_lower(arrayval, 1)..array_upper(arrayval, 1) loop
      qry := ''select objectdefinition.''|| arrayval[i];
      for aliasvalue in execute qry loop
        RAISE NOTICE ''field = %'', aliasvalue;
      end loop;
    end loop; 
   end loop;
 
 
So that everyone realizes what I am trying to do. I execute a function coltoparammatch to return a string list of field names in a given table. I am then executing a query to get a reference to the table I want to pull data from. I then loop in the array of column names and "try" to make a dynamic column reference to the recordset that the query is being held in. Alas no luck though.
 
TIA
Alex
Tom Lane <tgl@sss.pgh.pa.us> wrote:
A E writes:
> I tried to execute a dynamic sql string using the dynamic record
> column name but I getting this error: ERROR: syntax error at or near
> "into" at character 8. Does the execute statement not allow the into
> keyword

It does not :-(. The best way of getting data back from an EXECUTE'd
select is to use a FOR ... IN EXECUTE loop. See the docs.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org
Tom and Chris,
I tried to use a for in loop to execute the statement to try to get some dynamic functionality but I get "ERROR:  missing ".." at end of SQL expression" what am I doing wrong?
 
Code:
 
   qry := ''select * from ''|| trim(realname) ||'' where ''|| trim(searchfield) ||'' like ''''%''|| trim(searchvalue) ||''%'''''';   arrayval := string_to_array(coltoparammatch(3, talias, insertparams, insertdelimiter), '','');      for objectdefinition in execute qry loop    for i in array_lower(arrayval, 1)..array_upper(arrayval, 1) loop      qry := ''select objectdefinition.''|| arrayval[i];      for aliasvalue in execute qry loop        RAISE NOTICE ''field = %'', aliasvalue;      end loop;    end loop;    end loop;
 
 
So that everyone realizes what I am trying to do. I execute a function coltoparammatch to return a string list of field names in a given table. I am then executing a query to get a reference to the table I want to pull data from. I then loop in the array of column names and "try" to make a dynamic column reference to the recordset that the query is being held in. Alas no luck though.
 
TIA
AlexTom Lane <tgl@sss.pgh.pa.us> wrote:
A E writes:> I tried to execute a dynamic sql string using the dynamic record> column name but I getting this error: ERROR: syntax error at or near> "into" at character 8. Does the execute statement not allow the into> keywordIt does not :-(. The best way of getting data back from an EXECUTE'dselect is to use a FOR ... IN EXECUTE loop. See the docs.regards, tom lane---------------------------(end of broadcast)---------------------------TIP 6: Have you searched our list archives?http://archives.postgresql.org
Tue, 6 Jan 2004 07:45:31 -0800 (PST)
A E <cooljoint@yahoo.com>
Re: Anything akin to an Evaluate Statement in Postgresql? 
A E <cooljoint@yahoo.com> writes:
> I tried to use a for in loop to execute the statement to try to get some dynamic functionality but I get "ERROR:  missing ".." at end of SQL expression" what am I doing wrong?

Did you declare the loop variable (here,  "objectdefinition") as a
record or rowtype variable?  If the loop variable is not known,
plpgsql assumes this is a locally-declared-integer kind of FOR loop,
which leads it to expect the lowbound .. highbound kind of syntax,
which leads to the above error message.

			regards, tom lane

Tue, 06 Jan 2004 10:56:59 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Anything akin to an Evaluate Statement in Postgresql? 
objectdefinition is defined as a record variable. It works fine when I remove the statement trying to get the dynamically concocted string executed and the results placed into the aliasvalue variable which is varchar. 
 
My first question is, Can you perform a select on a variable? Such as in the case of executing the dynamic string of objectdefinition.[Whatever Value] 
 
My next question is do you have declare the variable being used in a for in execute as a record variable? If so is this by design or limitation?
 
My last question is has anyone else run into this before? Where the name of the column was unknown, and it was dynamically generated and needed to be turned into a reference instead of a string?
 
TIA
 
Alex 

Tom Lane <tgl@sss.pgh.pa.us> wrote:
A E writes:
> I tried to use a for in loop to execute the statement to try to get some dynamic functionality but I get "ERROR: missing ".." at end of SQL expression" what am I doing wrong?

Did you declare the loop variable (here, "objectdefinition") as a
record or rowtype variable? If the loop variable is not known,
plpgsql assumes this is a locally-declared-integer kind of FOR loop,
which leads it to expect the lowbound .. highbound kind of syntax,
which leads to the above error message.

regards, tom lane
objectdefinition is defined as a record variable. It works fine when I remove the statement trying to get the dynamically concocted string executed and the results placed into the aliasvalue variable which is varchar. 
 
My first question is, Can you perform a select on a variable? Such as in the case of executing the dynamic string of objectdefinition.[Whatever Value] 
 
My next question is do you have declare the variable being used in a for in execute as a record variable? If so is this by design or limitation?
 
My last question is has anyone else run into this before? Where the name of the column was unknown, and it was dynamically generated and needed to be turned into a reference instead of a string?
 
TIA
 
Alex Tom Lane <tgl@sss.pgh.pa.us> wrote:
A E writes:> I tried to use a for in loop to execute the statement to try to get some dynamic functionality but I get "ERROR: missing ".." at end of SQL expression" what am I doing wrong?Did you declare the loop variable (here, "objectdefinition") as arecord or rowtype variable? If the loop variable is not known,plpgsql assumes this is a locally-declared-integer kind of FOR loop,which leads it to expect the lowbound .. highbound kind of syntax,which leads to the above error message.regards, tom lane
Tue, 6 Jan 2004 08:24:24 -0800 (PST)
A E <cooljoint@yahoo.com>
Re: Anything akin to an Evaluate Statement in Postgresql? 