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

[dbalinglung]

>From: "A. Kretschmer" <[EMAIL PROTECTED]>
>
>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