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

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 convert mysql enum ('abc','def') to create a new table which would be referenced from the postgres table to provide the enum quality under the guise of "constraint REFERENCES ${table_name}_${column_name}_constraint_table." However, I will stop there 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 working copy is http://developer.postgresql.org/docs/pgsql/contrib/mysql/my2pg.pl which appears to have a working sequence generation 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