Thread: Anything akin to an Evaluate Statement in Postgresql?
Hi, Was wondering if there was anything akin to an evaluate statement in Postgresql for dynamic strings? Alex |
> 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;
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:
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
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
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;
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:
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
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
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:
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