Postgres Pro
Downloads
Home   >   mailing lists

Thread: extract and variables in PL/pgSQL

extract and variables in PL/pgSQL

From
Roberto Mello
Date:
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

Re: extract and variables in PL/pgSQL

From
Jan Wieck
Date:
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 #

Re: extract and variables in PL/pgSQL

From
Roberto Mello
Date:
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?