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

["dbalinglung" <alamsurya@xxxxxxxxxxxxxx>]

>From: "A. Kretschmer" <andreas.kretschmer@xxxxxxxxxxxxxx>

>

>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