problem creating function
Hello,
I am trying to create my first function, however I seem
to be stumbling into a problem I do not know the solution
to.
I keep getting the following error message:
ERROR: relation "activity" does not exist
KONTEXT: SQL function "test_get_activity"
The function I am trying to create looks like
this...
Code:
CREATE FUNCTION test_get_activity("fromDate" date, "toDate" date, "employeeSignature" "varchar") RETURNS SETOF record AS
$BODY$SELECT
Activity.EmployeeSignature,
Activity.Date,
Activity.End,
Activity.Start,
Activity.Note,
Activity.TypeID,
Activity.StatusCode,
ActivityType.FunctionCode,
ActivityType.Name
FROM
Activity, ActivityType
WHERE
($1 >= Activity.Date) and ($2 <= Activity.Date)
and
(Activity.EmployeeSignature == $3)$BODY$
LANGUAGE 'sql' VOLATILE;
ALTER FUNCTION test_get_activity(date, date, "varchar") OWNER TO postgres;
Any help to solve this problem is highly appreciated.
Kind Regards,
Diod
example function in postgres
The _really_ nice features of postgres functions require procedural languages to be installed. see
http://www.postgresql.org/docs/8.0/i...ve/xplang.html for details. You also can download the docs and use it locally.
if you setup the procedural language into your template1, all databases created afterwards will be able to use these languages.
pl/pgsql is a quite simple one - but you have variables, conditionals and loops.
an example of a very simple function written in this language would create a Customer ('kunde' in german) and a related mandant, setting the IDs making them joinable and return the auto-generated kndid (custumer-id).
Code:
create sequence mndidseq start 3100;
create sequence kndidseq start 150;
create or replace function "CreateKunde" (integer) returns integer as'
declare
an Alias for $1;
nmid integer;
nkid integer;
begin
nmid := nextval(''mndidseq'');
insert into "Mandant" ("MndID","Branche") values (nmid,''EDV'');
nkid := nextval(''kndidseq'');
insert into "KundeVon" ("KndID","AufN","AufG") values (nkid,an,nmid);
return nkid;
end;'
language plpgsql;
if you are used to ist, is very convenient to take the basic functionality out of your frontend and put it directly to the server - speed is also much better. And if you are tired to try to get Access to do what you want: use pl/pgsql
hth