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