Обсуждение: Table name as parameter

Поиск
Список
Период
Сортировка

Table name as parameter

От
Sairam Krishnamurthy
Дата:
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>';

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

Re: Table name as parameter

От
Adrian Klaver
Дата:
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

Re: Table name as parameter

От
Andreas Joseph Krogh
Дата:
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 |                                             |
------------------------+---------------------------------------------+