Postgres Pro
Downloads
Home   >   mailing lists

Thread: SELECT EXTRACT doesn't work with variables?

SELECT EXTRACT doesn't work with variables?

From
Conxita Marín
Date:
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);
        RETURN aux2;
END;
' LANGUAGE 'plpgsql';

I obtain this error:

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

Is this a known bug?
[Conxita Mar{in]

Re: SELECT EXTRACT doesn't work with variables?

From
Masaru Sugawara
Date:
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

Re: SELECT EXTRACT doesn't work with variables?

From
Tom Lane
Date:
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

Re: SELECT EXTRACT doesn't work with variables?

From
Conxita Marín
Date:

-----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.

Re: SELECT EXTRACT doesn't work with variables?

From
Conxita Marín
Date:
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.

Re: SELECT EXTRACT doesn't work with variables?

From
Conxita Marín
Date:
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?