X-Original-To: pgsql-novice-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.2])
	by svr1.postgresql.org (Postfix) with ESMTP
	id DA230D1D501; Sat, 31 Jan 2004 15:23:50 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024)
	with ESMTP id 50077-06; Sat, 31 Jan 2004 11:23:51 -0400 (AST)
Received: from megazone.bigpanda.com (megazone.bigpanda.com [64.147.171.210])
	by svr1.postgresql.org (Postfix) with ESMTP
	id C9D65D1B45B; Sat, 31 Jan 2004 11:23:47 -0400 (AST)
Received: by megazone.bigpanda.com (Postfix, from userid 1001)
	id E6F39357A3; Sat, 31 Jan 2004 07:23:36 -0800 (PST)
Received: from localhost (localhost [127.0.0.1])
	by megazone.bigpanda.com (Postfix) with ESMTP
	id E5A4E3579C; Sat, 31 Jan 2004 07:23:36 -0800 (PST)
Date: Sat, 31 Jan 2004 07:23:36 -0800 (PST)
From: Stephan Szabo <sszabo@megazone.bigpanda.com>
To: Pragati Kenkare <pkenkare@writeme.com>
Cc: pgsql-novice@postgresql.org, pgsql-sql@postgresql.org
Subject: Re: 'select nextval('seq_name');' in a function ?
In-Reply-To: <20040124090410.63451.qmail@iname.com>
Message-ID: <20040131072014.L91270@megazone.bigpanda.com>
References: <20040124090410.63451.qmail@iname.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200401/307
X-Sequence-Number: 9462

On Sat, 24 Jan 2004, Pragati Kenkare wrote:

> I am new to postgresql. Using PostgreSQL 7.3.2, I did the following.
>
> testdb#CREATE SEQUENCE principal_id increment 1 start 1000 cache 5;
>
> testdb#CREATE TABLE principal (principal_id int not null, name text, constraint pk_principal primary key(principal_id));
>
> testdb#CREATE FUNCTION getnext_principal_id(int) returns int as 'select nextval('principal_id');' language 'SQL';

You need to double the single quotes around principal_id, and I'm not sure
which version switched to int8 sequences, and do you really want to be
passing in an unused int?

CREATE FUNCTION getnext_principal_id() returns int as 'select
CAST(nextval(''principal_id'') AS int4);' language 'SQL';

However, how are you planning to use this?  Perhaps making principal_id a
serial would be better for you.