Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to write update function (stored procedure) in Postgresql?

Tags:

postgresql

I want to create an update function (stored procedure) in postgresql, I have searched many times on google, but didn't find a proper example of an update function (stored procedure). How can I write an update function in Postgresql and change the existing data in a table?

Thanks in advance.

Example of Function

CREATE OR REPLACE FUNCTION updateuser_login(userloginidp integer, usercategoryidf integer, usertypeidf integer, usertypereferenceidf integer, loginname text, loginpassword text, menutypeidf integer, username text, dashboardconfig text, careprovideridf integer, isactive boolean)
  RETURNS void AS
$BODY$BEGIN
    UPDATE  tbuserlogin
    SET usercategoryidf="@usercategoryidf", 
        usetypeidf="@usertypeidf", 
        usertypereferenceidf="@usertypereferenceidf", 
        loginname="@loginname", 
        loginpassword="@loginpassword", 
        menutypeidf="@menutypeidf", 
        username="@username", 
        dashboardconfig="@dashboardconfig", 
        careprovideridf="@careprovideridf", 
        isactive="@isactive"
    WHERE   userloginidp = "@userloginidp";
END$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION updateuser_login(integer, integer, integer, integer, text, text, integer, text, text, integer, boolean)
  OWNER TO postgres;
like image 716
Iren Patel Avatar asked Jan 13 '14 09:01

Iren Patel


People also ask

What is the correct method of changing a stored procedure PostgreSQL?

To change a procedure's schema, you must also have CREATE privilege on the new schema. To alter the owner, you must also be a direct or indirect member of the new owning role, and that role must have CREATE privilege on the procedure's schema.

How do I modify a function in PostgreSQL?

To start the function editor, navigate to the desired function in the schema browser, select the 'Source Code' tab and click on the 'Edit In Function Editor' button. Alternatively, you can right-click on the function in the schema browser and select 'Edit In Function Editor'.


2 Answers

You can find excellent examples of this kind of stuff in the PGXN site's source code:

https://github.com/pgxn/pgxn-manager/tree/master/sql

Example from the users sql file:

CREATE OR REPLACE FUNCTION update_user(
    nickname   LABEL,
    full_name  TEXT   DEFAULT NULL,
    email      EMAIL  DEFAULT NULL,
    uri        URI    DEFAULT NULL,
    twitter    CITEXT DEFAULT NULL
) RETURNS BOOLEAN LANGUAGE plpgsql SECURITY DEFINER AS $$
/*

    % SELECT update_user(
        nickname  := 'theory',
        full_name := 'David E. Wheeler',
        email     := '[email protected]',
        uri       := 'http://www.justatheory.com/',
        twitter   :- 'theory'
    );
     update_user 
    ─────────────
     t

Update the specified user. The user must be active. The nickname cannot be
changed. The password can only be changed via `change_password()` or
`reset_password()`. Pass other attributes as:

full_name
: The full name of the user.

email
: The email address of the user. Must be a valid email address as verified by
  [Email::Valid](http://search.cpan.org/perldoc?Email::Valid).

uri
: Optional URI for the user. Should be a valid URI as verified by
  [Data::Validate::URI](http://search.cpan.org/perldoc?Data::Validate::URI).

twitter
: Optional Twitter username. A leading "@" wil be removed.

Returns true if the user was updated, and false if not.

*/
BEGIN
    UPDATE users
       SET full_name      = COALESCE(update_user.full_name, users.full_name),
           email          = COALESCE(update_user.email,     users.email),
           uri            = COALESCE(update_user.uri,       users.uri),
           twitter        = COALESCE(trim(leading '@' FROM update_user.twitter), users.twitter),
           updated_at     = NOW()
     WHERE users.nickname = update_user.nickname
       AND users.status   = 'active';
    RETURN FOUND;
END;
$$;
like image 95
Denis de Bernardy Avatar answered Sep 24 '22 02:09

Denis de Bernardy


Postgresql reads double quotes as a name of column.. Replace it with single quote or you can just use your parameters..

CREATE OR REPLACE FUNCTION updateuser_login(userloginidp integer,
usercategoryidf integer, usertypeidf integer, usertypereferenceidf
integer, loginname text, loginpassword text, menutypeidf integer,
username text, dashboardconfig text, careprovideridf integer, isactive
boolean)   RETURNS void AS 
$BODY$
BEGIN
    UPDATE  tbuserlogin
    SET usercategoryidf = '@usercategoryidf', 
        usetypeidf = '@usertypeidf', 
        usertypereferenceidf = '@usertypereferenceidf', 
        loginname = '@loginname', 
        loginpassword = '@loginpassword', 
        menutypeidf = '@menutypeidf', 
        username = '@username', 
        dashboardconfig = '@dashboardconfig', 
        careprovideridf = '@careprovideridf', 
        isactive = '@isactive'
    WHERE   userloginidp = '@userloginidp'; 
END;
$BODY$   
LANGUAGE plpgsql VOLATILE   
COST 100; 
ALTER FUNCTION updateuser_login(integer, integer, integer, integer, text, text, integer, text, text, integer, boolean)   
OWNER TO postgres;
like image 44
darkangel Avatar answered Sep 24 '22 02:09

darkangel