Thread: plpgsql related question: intervals and variables

Hello out there,

We have a problem in plpgsql:
We want to add variable periods (result of a query) to a timestamp.

Our Code looks like this:

Begin
  heute := ''today'';
Select Into vk ourcolumn From table where other = foo;  If vk > 0 Thenvk_txt := ''Vorkuehlung notwendig'';        ez
:=heute + interval ''vk days'';       Else     vk_txt := ''Keine Vorkuehlung vorgeschrieben'';  End if;
 

We get the following:
ERROR:  Bad interval external representation 'vk days'

The variable 'heute' is declared as timestamp,
'vk' as integer!

What have we done wrong??

:(

Thanks in advance,

Willi, Albin

--
=============================
Wilhelm Graiss
Altirdning 12
8952 Irdning
03682/22451/267

Willhelm,

> Begin
>
>    heute := ''today'';
> Select Into vk ourcolumn From table where other = foo;
>    If vk > 0 Then
>     vk_txt := ''Vorkuehlung notwendig'';
>     ez  := heute + interval ''vk days'';

PL/pgSQL handles variable like SQL, not like PHP or Perl.  You can't do a
variable substitution inside quotes, and you need to cast:

ez  := heute + interval (cast(vk as text) ||  '' days'');

Also, the string 'today' has no special meaning in PL/pgSQL.  I think you want
now() instead.

I'm afraid that you're going to need a tutorial on SQL datatypes, casting, and
similar issues ... I wish I had one to recommend to you.  Just keep in mind
that SQL scripting languages (like PL/pgSQL) are not Perl!

--
-Josh BerkusAglio Database SolutionsSan Francisco

On Tuesday 21 October 2003 14:58, Wilhelm Graiss wrote:

>    heute := ''today'';
> Select Into vk ourcolumn From table where other = foo;
>    If vk > 0 Then
>     vk_txt := ''Vorkuehlung notwendig'';
>     ez  := heute + interval ''vk days'';

> The variable 'heute' is declared as timestamp,
> 'vk' as integer!
>
> What have we done wrong??

Quoted the vk variable. You want something like:

ez := heute + (vk || '' days'')::interval;

--  Richard Huxton Archonet Ltd

On Tue, 21 Oct 2003, Josh Berkus wrote:

> >    heute := ''today'';
> > Select Into vk ourcolumn From table where other = foo;
> >    If vk > 0 Then
> >     vk_txt := ''Vorkuehlung notwendig'';
> >     ez  := heute + interval ''vk days'';
>
> PL/pgSQL handles variable like SQL, not like PHP or Perl.  You can't do a
> variable substitution inside quotes, and you need to cast:
>
> ez  := heute + interval (cast(vk as text) ||  '' days'');

I think that something likeez := heute + vk * interval '1 day';
might be better in general.