SELECT EXTRACT doesn't work with variables?

On Wed, 18 Dec 2002 14:03:27 +0100
Conxita Mar�n <comarin@telefonica.net> wrote:

I try to do something like this:

CREATE FUNCTION prova() RETURNS numeric(20,0) AS
'
DECLARE
aux TIMESTAMP;
aux2 numeric(20,0);
BEGIN
aux = ''01.01.2002 00:00:00 CET'';
aux2 = SELECT EXTRACT(EPOCH FROM TIMESTAMP aux);

Instead of this line, I think you need to execute the following statement:

SELECT INTO aux2 EXTRACT(EPOCH FROM aux);

Regards,
Masaru Sugawara

Masaru Sugawara <rk73@sea.plala.or.jp> writes:

Conxita Mar�n <comarin@telefonica.net> wrote:

aux2 = SELECT EXTRACT(EPOCH FROM TIMESTAMP aux);

Instead of this line, I think you need to execute the following statement:

SELECT INTO aux2 EXTRACT(EPOCH FROM aux);

Or

aux2 = EXTRACT(EPOCH FROM TIMESTAMP aux);

regards, tom lane

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: mi�rcoles, 18 de diciembre de 2002 17:00
To: Masaru Sugawara
Cc: cmarin@dims.com; pgsql-general@postgresql.org
Subject: Re: [GENERAL] SELECT EXTRACT doesn't work with variables?

Masaru Sugawara <rk73@sea.plala.or.jp> writes:

Conxita Mar�n <comarin@telefonica.net> wrote:

aux2 = SELECT EXTRACT(EPOCH FROM TIMESTAMP aux);

Instead of this line, I think you need to execute the following statement:

SELECT INTO aux2 EXTRACT(EPOCH FROM aux);

Or

aux2 = EXTRACT(EPOCH FROM TIMESTAMP aux);

This works: SELECT INTO aux2 EXTRACT(EPOCH FROM aux);

This doesn't work: aux2 = EXTRACT(EPOCH FROM TIMESTAMP aux);

NOTICE: Error occurred while executing PL/pgSQL function prova
NOTICE: line 9 at assignment
ERROR: parser: parse error at or near "$1"

Thanks to all.

Conxita.

From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: mi�rcoles, 18 de diciembre de 2002 17:00
To: Masaru Sugawara
Cc: cmarin@dims.com; pgsql-general@postgresql.org
Subject: Re: [GENERAL] SELECT EXTRACT doesn't work with variables?

Masaru Sugawara <rk73@sea.plala.or.jp> writes:

Conxita Mar�n <comarin@telefonica.net> wrote:

aux2 = SELECT EXTRACT(EPOCH FROM TIMESTAMP aux);

Instead of this line, I think you need to execute the following statement:

SELECT INTO aux2 EXTRACT(EPOCH FROM aux);

Or

aux2 = EXTRACT(EPOCH FROM TIMESTAMP aux);

This works: SELECT INTO aux2 EXTRACT(EPOCH FROM aux);

This doesn't work: aux2 = EXTRACT(EPOCH FROM TIMESTAMP aux);

NOTICE: Error occurred while executing PL/pgSQL function prova
NOTICE: line 9 at assignment
ERROR: parser: parse error at or near "$1"

Thanks to all.

Conxita.

From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: mi�rcoles, 18 de diciembre de 2002 17:00
To: Masaru Sugawara
Cc: cmarin@dims.com; pgsql-general@postgresql.org
Subject: Re: [GENERAL] SELECT EXTRACT doesn't work with variables?

Masaru Sugawara <rk73@sea.plala.or.jp> writes:

Conxita Mar�n <comarin@telefonica.net> wrote:

aux2 = SELECT EXTRACT(EPOCH FROM TIMESTAMP aux);

Instead of this line, I think you need to execute the following statement:

SELECT INTO aux2 EXTRACT(EPOCH FROM aux);

Or

aux2 = EXTRACT(EPOCH FROM TIMESTAMP aux);

This works: SELECT INTO aux2 EXTRACT(EPOCH FROM aux);

This doesn't work: aux2 = EXTRACT(EPOCH FROM TIMESTAMP aux);

NOTICE: Error occurred while executing PL/pgSQL function prova
NOTICE: line 9 at assignment
ERROR: parser: parse error at or near "$1"

Thanks to all.

Conxita.

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?