dcsimg
Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2005
    Location
    Sweden
    Posts
    4

    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

  2. #2
    Join Date
    Sep 2005
    Location
    Sweden
    Posts
    4
    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

  3. #3
    Join Date
    Sep 2005
    Location
    Sweden
    Posts
    4
    It turned out that there were
    an error in the sql creation. It
    is now fixed and works as it should.

  4. #4
    Join Date
    Oct 2005
    Posts
    5

    Function Creation Basics

    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

  5. #5
    Join Date
    Sep 2005
    Location
    Sweden
    Posts
    4
    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.

  6. #6
    Join Date
    Oct 2005
    Posts
    8

    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
    Last edited by lgkf; 10-29-2005 at 07:29 AM. Reason: code tags missing ;(

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •