Postgres Pro
Downloads
Home   >   mailing lists

Thread: Stored procs / functions - execution failure

Stored procs / functions - execution failure

From
Damian Georgiou
Date:
Hi All,

I am having an issue with a function where it used to run in a previous installation of postgres under windows. The box has since been decommissioned so I am unable to check exactly what version it was though it was version 8 under winxp.

I am now running postgres 8.2.5 Under OSX 10.5.3

Now I have imported the data, the function is available in the list however I get this error when I try to run it





ERROR:  function sp_schedulefromdate("unknown") does not exist
LINE 1: select sp_scheduleFromDate('2008-01-01');
               ^
HINT:  No function matches the given name and argument types. You may need to add explicit type casts.

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

ERROR: function sp_schedulefromdate("unknown") does not exist
SQL state: 42883
Hint: No function matches the given name and argument types. You may need to add explicit type casts.
Character: 8






The function code is as below

-- Function: "sp_scheduleFromDate"(date) -- DROP FUNCTION "sp_scheduleFromDate"(date); CREATE OR REPLACE FUNCTION "sp_scheduleFromDate"(date)  RETURNS refcursor AS $BODY$declare    v_date alias for $1;    v_rs cursor for select            v.sitename,           ...        from v_schedule v                where v.scheduledate = v_date::text; begin    open v_rs;    return v_rs; end;         $BODY$  LANGUAGE 'plpgsql' VOLATILE; ALTER FUNCTION "sp_scheduleFromDate"(date) OWNER TO postgres; GRANT EXECUTE ON FUNCTION "sp_scheduleFromDate"(date) TO public; GRANT EXECUTE ON FUNCTION "sp_scheduleFromDate"(date) TO postgres; GRANT EXECUTE ON FUNCTION "sp_scheduleFromDate"(date) TO divequee_dmpro;

Any thoughts?

Damian

Re: Stored procs / functions - execution failure

From
Tommy Gildseth
Date:
Damian Georgiou wrote:
> Hi All,
>
> I am having an issue with a function where it used to run in a previous
> installation of postgres under windows. The box has since been
>
> ERROR:  function sp_schedulefromdate("unknown") does not exist
> LINE 1: select sp_scheduleFromDate('2008-01-01');
>                ^
> HINT:  No function matches the given name and argument types. You may
> need to add explicit type casts.


I suppose you need to add an explicit cast, as it says in the error
message. This means that instead of sp_scheduleFromDate('2008-01-01');
which is what you got now, you use
sp_scheduleFromDate('2008-01-01'::date); which seems to be what the
function expects.



--
Tommy Gildseth

Re: Stored procs / functions - execution failure

From
Craig Ringer
Date:
Damian Georgiou wrote:

> I am having an issue with a function where it used to run in a previous
> installation of postgres under windows. The box has since been
> decommissioned so I am unable to check exactly what version it was though it
> was version 8 under winxp.
>
> I am now running postgres 8.2.5 Under OSX 10.5.3
>
> Now I have imported the data, the function is available in the list however
> I get this error when I try to run it

> ERROR:  function sp_schedulefromdate("unknown") does not exist
> LINE 1: select sp_scheduleFromDate('2008-01-01');

OK, first guess: you've defined your function with a mixed case name,
and you're being bitten by case folding. You probably meant to write:

    select "sp_scheduleFromDate"('2008-01-01')

not

    select sp_scheduleFromDate('2008-01-01')

The former suppresses lowercase folding; the latter does not.

--
Craig Ringer