pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedextract and variables in PL/pgSQL
3+ messages / 2 participants
[nested] [flat]
* extract and variables in PL/pgSQL
@ 2002-06-05 18:20 Roberto Mello <rmello@cc.usu.edu>
2002-06-06 12:20 ` Re: extract and variables in PL/pgSQL Jan Wieck <janwieck@yahoo.com>
0 siblings, 1 reply; 3+ messages in thread
From: Roberto Mello @ 2002-06-05 18:20 UTC (permalink / raw)
To: pgsql-sql
Hi,
Someone in another list asked about this function. I'm not sure why it
doesn't work. What am I missing?
CREATE FUNCTION zastras(timestamp) RETURNS integer AS '
declare
w_temp1 alias for $1;
w_result integer;
begin
SELECT INTO w_result EXTRACT(epoch from timestamp w_temp1);
return w_result;
end;
' language 'plpgsql';
NOTICE: Error occurred while executing PL/pgSQL function zastras
NOTICE: line 5 at select into variables
Thanks,
-Roberto
--
+----| http://fslc.usu.edu/ USU Free Software & GNU/Linux Club |------+
Roberto Mello - Computer Science, USU - http://www.brasileiro.net/
http://www.sdl.usu.edu/ - Space Dynamics Lab, Developer
I wonder what this button does? *&^(&^)#@$*&_% NO CARRIER
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: extract and variables in PL/pgSQL
2002-06-05 18:20 extract and variables in PL/pgSQL Roberto Mello <rmello@cc.usu.edu>
@ 2002-06-06 12:20 ` Jan Wieck <janwieck@yahoo.com>
2002-06-06 16:10 ` Re: extract and variables in PL/pgSQL Roberto Mello <rmello@cc.usu.edu>
0 siblings, 1 reply; 3+ messages in thread
From: Jan Wieck @ 2002-06-06 12:20 UTC (permalink / raw)
To: Roberto Mello <rmello@cc.usu.edu>; +Cc: pgsql-sql
Roberto Mello wrote:
> Hi,
>
> Someone in another list asked about this function. I'm not sure why it
> doesn't work. What am I missing?
>
> CREATE FUNCTION zastras(timestamp) RETURNS integer AS '
> declare
> w_temp1 alias for $1;
> w_result integer;
> begin
> SELECT INTO w_result EXTRACT(epoch from timestamp w_temp1);
> return w_result;
>
> end;
> ' language 'plpgsql';
>
> NOTICE: Error occurred while executing PL/pgSQL function zastras
> NOTICE: line 5 at select into variables
First you're missing the error message, which should read
something like
ERROR: parser: parse error at or near "$1"
Doesn't help much here, but it is usually a good idea to
include error messages in reports like this.
I'm not 100% sure, but it seems that the PostgreSQL main
parser doesn't support parameters in the place of this
"timestamp" expression construct.
It works if you leave out the keyword "timestamp" writing
SELECT INTO w_result EXTRACT(epoch from w_temp1);
because the variable w_temp1 is known to be of type timestamp
already and therefore the a_expr node on the right hand side
of from is valid.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: extract and variables in PL/pgSQL
2002-06-05 18:20 extract and variables in PL/pgSQL Roberto Mello <rmello@cc.usu.edu>
2002-06-06 12:20 ` Re: extract and variables in PL/pgSQL Jan Wieck <janwieck@yahoo.com>
@ 2002-06-06 16:10 ` Roberto Mello <rmello@cc.usu.edu>
0 siblings, 0 replies; 3+ messages in thread
From: Roberto Mello @ 2002-06-06 16:10 UTC (permalink / raw)
To: Jan Wieck <janwieck@yahoo.com>; +Cc: pgsql-sql
On Thu, Jun 06, 2002 at 08:20:55AM -0400, Jan Wieck wrote:
> >
> > NOTICE: Error occurred while executing PL/pgSQL function zastras
> > NOTICE: line 5 at select into variables
>
> First you're missing the error message, which should read
> something like
>
> ERROR: parser: parse error at or near "$1"
>
> Doesn't help much here, but it is usually a good idea to
> include error messages in reports like this.
Sorry Jan, I meant to paste that.
> It works if you leave out the keyword "timestamp" writing
>
> SELECT INTO w_result EXTRACT(epoch from w_temp1);
>
> because the variable w_temp1 is known to be of type timestamp
> already and therefore the a_expr node on the right hand side
> of from is valid.
Oh, okay. Thanks.
-Roberto
--
+----| http://fslc.usu.edu/ USU Free Software & GNU/Linux Club |------+
Roberto Mello - Computer Science, USU - http://www.brasileiro.net/
http://www.sdl.usu.edu/ - Space Dynamics Lab, Developer
Daddy, why do those people have to use Microsoft Windows?
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2002-06-06 16:10 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-06-05 18:20 extract and variables in PL/pgSQL Roberto Mello <rmello@cc.usu.edu>
2002-06-06 12:20 ` Jan Wieck <janwieck@yahoo.com>
2002-06-06 16:10 ` Roberto Mello <rmello@cc.usu.edu>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox