Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2009
    Posts
    37

    Unanswered: Calling Stored procedure with parameter

    Hi I am trying to write a function in postgresql that uses a parameter and returns a type that is fields from a table. The function is created successfully however I am unable to call the function. The problem is that I don't know the correct syntax to call the function so I am not sure if I have written the function correctly. Here is the code that I have written for the Type and the Function.
    Thanks
    Liam

    CREATE TYPE GetUser AS (FirstName text, LastName text,
    Email text, Password text, Phone text, Address text,
    AccountAdmin boolean, BranchID integer, UserGroupID integer,
    AccountID integer, LastLogon timestamp without time zone,
    PreviousLastLogon timestamp without time zone,
    UserActivated timestamp without time zone,
    ShowProjectManagement boolean, SupportCountryID smallint,
    PasswordChanged timestamp without time zone,
    DefaultProposalCurrencyID smallint );

    CREATE FUNCTION user_getuser(IN "UserID" bigint[])
    RETURNS SETOF getusertype AS
    $$
    DECLARE
    r RECORD;
    BEGIN
    FOR r IN (SELECT
    "tblUser"."FirstName",
    "tblUser"."LastName",
    "tblUser"."Email",
    "tblUser"."Password",
    "tblUser"."Phone",
    "tblUser"."Address",
    "tblUser"."AccountAdmin",
    "tblUser"."BranchID",
    "tblUser"."UserGroupID",
    "tblUser"."AccountID",
    "tblUser"."LastLogon",
    "tblUser"."PreviousLastLogon",
    "tblUser"."UserActivated",
    "tblUser"."ShowProjectManagement",
    "tblUser"."SupportCountryID",
    "tblUser"."PasswordChanged",
    "tblUser"."DefaultProposalCurrencyID"

    FROM
    public."tblUser"

    WHERE
    "tblUser"."UserID" = @UserID) LOOP

    RETURN NEXT r;
    END LOOP;
    END;
    $$LANGUAGE 'plpgsql';

  2. #2
    Join Date
    Sep 2009
    Posts
    39
    if you have this situations just create a view.

    CREATE OR REPLACE VIEW myview AS
    (SELECT
    "tblUser"."FirstName",
    "tblUser"."LastName",
    "tblUser"."Email",
    "tblUser"."Password",
    "tblUser"."Phone",
    "tblUser"."Address",
    "tblUser"."AccountAdmin",
    "tblUser"."BranchID",
    "tblUser"."UserGroupID",
    "tblUser"."AccountID",
    "tblUser"."LastLogon",
    "tblUser"."PreviousLastLogon",
    "tblUser"."UserActivated",
    "tblUser"."ShowProjectManagement",
    "tblUser"."SupportCountryID",
    "tblUser"."PasswordChanged",
    "tblUser"."DefaultProposalCurrencyID"

    FROM
    public."tblUser"

    ALTER TABLE myview OWNER TO postgres;
    GRANT ALL ON TABLE myview TO postgres;

    and select from view is:
    select * from myview where "tblUser"."UserID"=......

    if you have call function

    select * from user_getuser(1)
    1 is your parameter.

    your function is wrong.

  3. #3
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    I would write this function in sql not pl/pgsql (actually I'd probably do it as a view). And you "probably" don't need the custom composite type.

    Code:
    CREATE OR REPLACE FUNCTION user_getuser(userid int8)
    RETURNS TABLE (firstName TEXT...) AS
    $$
      SELECT firstName ...
      FROM public.tblUser
      WHERE userID = $1;
    $$ LANGUAGE 'sql' STABLE;
    
    -- Usage --
    SELECT * FROM user_getuser(100);
    -- or 
    SELECT (user_getuser(100)).*

  4. #4
    Join Date
    Oct 2009
    Posts
    37
    Thanks guys for your replys. Artacus that works, great help.

Posting Permissions

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