Home > mailing lists

Thread: Put variable values on time interval (from : Re: Get interval in months)

Put variable values on time interval (from : Re: Get interval in months)

From

"dbalinglung"

Date:

10 November 2008, 06:00:56

>From: "A. Kretschmer" <andreas.kretschmer@schollglas.com>

>select into v_output ((v_timeout - v_timein) ...
>
>Done, works for me.
>

ok, maybe you mean :

select ((v_timeout - v_timein) - interval ''v_timebreak minutes'') into v_output;

and then if i try to execute my function on pgAdmin with command :

select scmaster.pr_gettimeinterval('0830'::time,'1700'::time,60);

i got error message :

NOTICE: -- BOF --
ERROR: invalid input syntax for type interval: "v_timebreak minutes"
CONTEXT: SQL statement "SELECT (( $1 - $2 ) - interval 'v_timebreak minutes')"
PL/pgSQL function "pr_gettimeinterval" line 7 at select into variables

********** Error **********

ERROR: invalid input syntax for type interval: "v_timebreak minutes"
SQL state: 22007
Context: SQL statement "SELECT (( $1 - $2 ) - interval 'v_timebreak minutes')"
PL/pgSQL function "pr_gettimeinterval" line 7 at select into variables

if i defined the query with :

SELECT (( $1 - $2 ) - interval '60 minutes') into v_output

it's work but how can i changed my numeric values '60 minutes' into variable on function, so i can put other value.

Thank you again

Alam Surya

--------------------------

OLD MESSAGE :

--------------------------

Dear Expert,

I have a function to getting time interval bellow :

create or replace function scmaster.pr_gettimeinterval(time without time zone, time without time zone, numeric(5,2)) returns char(10) As '
declare v_timein    alias for $1;
        v_timeout   alias for $2;
        v_timebreak alias for $3;
        v_output    char(10);
begin
raise notice ''-- BOF --'';
v_output := select ((v_timeout - v_timein) - interval ''v_timebreak minutes'');

raise notice ''-- EOF --'';
return v_output;
end;'
language plpgsql;

and when i compilled from pgAdmin, i got some error message bellow :

ERROR: syntax error at or near "select"
LINE 1: SELECT select (( $1 - $2 ) - interval 'v_timebreak minute...
^
QUERY: SELECT select (( $1 - $2 ) - interval 'v_timebreak minutes')
CONTEXT: SQL statement in PL/PgSQL function "pr_gettimeinterval" near line 7

********** Error **********

ERROR: syntax error at or near "select"
SQL state: 42601
Context: SQL statement in PL/PgSQL function "pr_gettimeinterval" near line 7

How can i to put my variable "v_timebreak" into function ? so i can send dynamic value for v_timebreak.

please help, thank you.

Alam Surya