Thread: Table name as parameter
All,
Thanks,
Sairam Krishnamurthy
+1 612 859 8161
I have a function that takes the table name the parameter. After some digging I found that this can be made possible by have the query as a string and EXECUTE it.
EXECUTE 'SELECT * FROM "' || table || '" WHERE <condition>';
The above works.
But I want the result in a record variable for further processing. So my query actually is
EXECUTE 'SELECT * FROM "' || table || '" INTO "record_data" WHERE <condition>';
This one will not work with the following error message:
ERROR: syntax error at or near "INTO"
Can some one help me ?
Thanks,
Sairam Krishnamurthy
+1 612 859 8161
On 05/09/2011 12:33 PM, Sairam Krishnamurthy wrote: > All, > > I have a function that takes the table name the parameter. After some > digging I found that this can be made possible by have the query as a > string and EXECUTE it. > > EXECUTE 'SELECT * FROM "' || table || '" WHERE <condition>'; > > The above works. > > But I want the result in a record variable for further processing. So my > query actually is > > EXECUTE 'SELECT * FROM "' || table || '" INTO "record_data" WHERE > <condition>'; Try.: EXECUTE 'SELECT * FROM "' || table || '" WHERE <condition>' INTO record_data; > > This one will not work with the following error message: > > ERROR: syntax error at or near "INTO" > > > Can some one help me ? > > Thanks, > Sairam Krishnamurthy > +1 612 859 8161 -- Adrian Klaver adrian.klaver@gmail.com
On 05/09/2011 10:59 PM, Adrian Klaver wrote: > On 05/09/2011 12:33 PM, Sairam Krishnamurthy wrote: >> All, >> >> I have a function that takes the table name the parameter. After some >> digging I found that this can be made possible by have the query as a >> string and EXECUTE it. >> >> EXECUTE 'SELECT * FROM "' || table || '" WHERE <condition>'; >> >> The above works. >> >> But I want the result in a record variable for further processing. So my >> query actually is >> >> EXECUTE 'SELECT * FROM "' || table || '" INTO "record_data" WHERE >> <condition>'; > > Try.: > > EXECUTE 'SELECT * FROM "' || table || '" WHERE > <condition>' INTO record_data; Or even safer (to avoid SQL-injection attacs): EXECUTE 'SELECT * FROM ' || quote_ident(table_name) || ' WHERE some_column = ' || quote_literal(some_value) -- Andreas Joseph Krogh <andreak@officenet.no> Senior Software Developer / CTO Public key: http://home.officenet.no/~andreak/public_key.asc ------------------------+---------------------------------------------+ OfficeNet AS | The most difficult thing in the world is to | Rosenholmveien 25 | know how to do a thing and to watch | 1414 Trollåsen | somebody else doing it wrong, without | NORWAY | comment. | Org.nr: NO 981 479 076 | | | | Tlf: +47 24 15 38 90 | | Fax: +47 24 15 38 91 | | Mobile: +47 909 56 963 | | ------------------------+---------------------------------------------+