Thread: selecting into a variable like @var=select ...

hello list,

I want to do something like the following:

address=# @var = select max(id) from passwd;
ERROR:  parser: parse error at or near "@" at character 1
address=# var = select max(id) from passwd;
ERROR:  parser: parse error at or near "var" at character 1
address=# :var = select max(id) from passwd;
ERROR:  parser: parse error at or near ":" at character 1
address=#

so that I could then issue

create sequence passwd_id_seq start @var increment 1

how can I declare and capture output into this variable? Or, should I create a function, if it is possible to issue a
"create"statement inside a procedure body? 

--
joe speigle

On Tuesday 16 December 2003 17:12, joseph speigle wrote:
> hello list,
>
> I want to do something like the following:
>
> address=# @var = select max(id) from passwd;
> ERROR:  parser: parse error at or near "@" at character 1

> so that I could then issue
>
> create sequence passwd_id_seq start @var increment 1
>
> how can I declare and capture output into this variable? Or, should I
> create a function, if it is possible to issue a "create" statement inside a
> procedure body?

You'll have to use a function (plpgsql would be the obvious choice). You can
issue almost all statements by building them up in a string and using
EXECUTE.

--
  Richard Huxton
  Archonet Ltd

thanks for your help.  I have put off this project.  Of the two mysql2pgsql scripts at
http://developer.postgresql.org/docs/pgsql/contrib/mysql,I edited "mysql2pgsql" (perl script) for a few hours to
convertmysql enum ('abc','def') to create a new table which would be referenced from the postgres table to provide the
enumquality under the guise of "constraint REFERENCES ${table_name}_${column_name}_constraint_table."  However, I will
stopthere and will not fix the sequence creation part of
http://developer.postgresql.org/docs/pgsql/contrib/mysql/mysql2pgsql(same file as the one at
http://sourceforge.net/projects/docman/)which is broken at this moment.  I submitted my fix as a patch to
http://sourceforge.net/projects/docman/which should reach the gentleman in charge of it.  However I think the better
workingcopy is http://developer.postgresql.org/docs/pgsql/contrib/mysql/my2pg.pl which appears to have a working
sequencegeneration routine. 

On Tue, Dec 16, 2003 at 06:12:40PM +0000, Richard Huxton wrote:
> On Tuesday 16 December 2003 17:12, joseph speigle wrote:
> > hello list,
> >
> > I want to do something like the following:
> >
> > address=# @var = select max(id) from passwd;
> > ERROR:  parser: parse error at or near "@" at character 1
>
> > so that I could then issue
> >
> > create sequence passwd_id_seq start @var increment 1
> >
> > how can I declare and capture output into this variable? Or, should I
> > create a function, if it is possible to issue a "create" statement inside a
> > procedure body?
>
> You'll have to use a function (plpgsql would be the obvious choice). You can
> issue almost all statements by building them up in a string and using
> EXECUTE.
>
> --
>   Richard Huxton
>   Archonet Ltd
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faqs/FAQ.html

--
joe speigle

On Tue, 2003-12-16 at 13:12, Richard Huxton wrote:
> On Tuesday 16 December 2003 17:12, joseph speigle wrote:
> > hello list,
> >
> > I want to do something like the following:
> >
> > address=# @var = select max(id) from passwd;
> > ERROR:  parser: parse error at or near "@" at character 1
>
> > so that I could then issue
> >
> > create sequence passwd_id_seq start @var increment 1
> >
> > how can I declare and capture output into this variable? Or, should I
> > create a function, if it is possible to issue a "create" statement inside a
> > procedure body?
>
> You'll have to use a function (plpgsql would be the obvious choice). You can
> issue almost all statements by building them up in a string and using
> EXECUTE.
>

why not just do:

begin;
create sequence passwd_id_seq;
select setval('passwd_id_seq',(select max(id) from passwd));
commit;


Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL