Thread: How can I use parameters in plain sql
How can I use parameters in plain sql like sql server.
FICTIONAL example that works for sql server:
declare @i int;
set @i = 1;
select * from mytable where id=@i;
FICTIONAL example that works for sql server:
declare @i int;
set @i = 1;
select * from mytable where id=@i;
On Fri, Sep 3, 2010 at 2:45 PM, John Adams <john_adams_mail@yahoo.com> wrote: > How can I use parameters in plain sql like sql server. > > FICTIONAL example that works for sql server: > declare @i int; > set @i = 1; > select * from mytable where id=@i; postgresql doesn't support variables in plain sql. psql has some client side manged variables, and you can of course use pl/pgsql. merlin
On Fri, Sep 3, 2010 at 3:47 PM, John Adams <john_adams_mail@yahoo.com> wrote: >> psql has some client side manged variables, and you can of course use >> pl/pgsql. > Do you mean I should use a pl/pgsql stored procedure or do I have to somehow > mark the sql as pl/pgsql? How? > Because in sql server it is all the same i.e. plain sql=tsql pl/pgsql is only used in functions: create function foo(i int) returns setof mytable as $$ begin return query select * from mytable where id = i; end; $$ language plpgsql; then plpgsql is reserved for fancy things that are tricky to do with plain sql. it's got loops, robust error handling, etc. http://www.postgresql.org/docs/8.4/static/plpgsql.html merlin
2010/9/3 Merlin Moncure <mmoncure@gmail.com>: > On Fri, Sep 3, 2010 at 3:47 PM, John Adams <john_adams_mail@yahoo.com> wrote: >>> psql has some client side manged variables, and you can of course use >>> pl/pgsql. >> Do you mean I should use a pl/pgsql stored procedure or do I have to somehow >> mark the sql as pl/pgsql? How? >> Because in sql server it is all the same i.e. plain sql=tsql > > pl/pgsql is only used in functions: > > create function foo(i int) returns setof mytable as > $$ > begin > return query select * from mytable where id = i; > end; > $$ language plpgsql; > > then > > > plpgsql is reserved for fancy things that are tricky to do with plain > sql. it's got loops, robust error handling, etc. > > http://www.postgresql.org/docs/8.4/static/plpgsql.html > Also, in PostgreSQL 9.0 you can have anonymous code blocks with 'DO'. http://www.postgresql.org/docs/9.0/static/sql-do.html -- Cédric Villemain 2ndQuadrant http://2ndQuadrant.fr/ PostgreSQL : Expertise, Formation et Support