Thread: use of alias

Hello Everybody,<br /><br />I am trying to change a oracle function to postgreSQL. I have to use the function parameter
indifferent query so i need to use alias command.<br />but i am not able to use this alias in "from  clause" of select
statment.could anybody please let me know how can i use it in from clause.i am getting following error while executing
thefunciton..<br /><br />MT ALIAS FOR  $1;<br />ST ALIAS FOR  $2;<br /><br />select  count(*) into count1 from MT,ST
whereMT.phonenumber=ST.phonenumber;<br /><br /><b>ERROR:  syntax error at or near "$1"<br />LINE 1: select count(*)
from $1 , $2  where MT.phonenumber=ST.phone...<br /><br />Thanx in Advance for all of your suggestion....</b><br /><br
/>

you could do this with 'execute' like so:

execute 'select  count(*) into count1 from ' || MT || ',' || ST || ' 
where ' || MT || '.phonenumber= ' || ST || '.phonenumber' into v_variable;

brett

Amit jain wrote:
> Hello Everybody,
>
> I am trying to change a oracle function to postgreSQL. I have to use 
> the function parameter in different query so i need to use alias command.
> but i am not able to use this alias in "from  clause" of select 
> statment. could anybody please let me know how can i use it in from 
> clause.i am getting following error while executing the funciton..
>
> MT ALIAS FOR  $1;
> ST ALIAS FOR  $2;
>
> select  count(*) into count1 from MT,ST where 
> MT.phonenumber=ST.phonenumber;
>
> *ERROR:  syntax error at or near "$1"
> LINE 1: select count(*) from  $1 , $2  where MT.phonenumber=ST.phone...
>
> Thanx in Advance for all of your suggestion....*
>

Though to be safe you should be quoting MT and ST with quote_ident()
before putting them into a dynamic statement.

Cheers
BJ

On Feb 12, 2008 4:38 PM, Brett McBride <brett@deakin.edu.au> wrote:
> you could do this with 'execute' like so:
>
> execute 'select  count(*) into count1 from ' || MT || ',' || ST || '
> where ' || MT || '.phonenumber= ' || ST || '.phonenumber' into v_variable;
>
> brett