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