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

Further digging has made me believe that
I can not use several tables in the select
statement within a function, is this true?

I tried another approach where I first created
a view to hold the proper columns from the
two different tables and then use a select
statement to retrieve the rows I wanted.
This did the trick.

My followup question is therefor, is this the
"proper" way of doing it. That is, should I use
the view approach instead?

Cheers,
Diod

It turned out that there were
an error in the sql creation. It
is now fixed and works as it should.

Hi,

I'm glad to see you successfully made your function. Is there any chance that you could point out the problem and provide the solution? I did not see the error in your code and will be making my first functions (sounds like a kids book ;) ) very soon.

Best,

Keith

Hello Keith,

Actually it was a problem with the naming
of my columns/tables nothing related to
functions or sql at all.

I am fairly new to postgresql and functions
however if you need some help just send me
an e-mail or something.

Cheers.

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