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

Question

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;

what is a updade function? I don't know this term. What this function should to do? — Pavel Stehule
– Pavel Stehule, Commented Jan 13, 2014 at 9:32
In PostgreSQL terminology it is void scalar function with embedded SQL statement. It can be implemented in SQL or PL/pgSQL language. A name "update function" is not usual. — Pavel Stehule
– Pavel Stehule, Commented Jan 13, 2014 at 11:36

Denis de Bernardy · Accepted Answer · 2014-01-13 10:43:42Z

27

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;
$$;

answered Jan 13, 2014 at 10:43

Denis de Bernardy

79.1k14 gold badges138 silver badges158 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Denis de Bernardy Over a year ago

@IrenPatel: that is Postgres. See the language: plpgsql. (And David Wheeler is one of the Postgres contributors.)

Craig Ringer Over a year ago

@IrenPatel Also, PostgreSQL is an SQL database. I'm guessing what you mean by "SQL" is "Microsoft SQL Server". Best to say so around here, "SQL" is just the language, so you'll confuse people when you say "SQL" and mean "MS-SQL"

Iren Patel Over a year ago

@Denis i try you suggestion in my demo function that work but when i try this in my main function that time generate error 'missing FROM-clause entry for table' what solution for this.

Denis de Bernardy Over a year ago

@IrenPatel: using the latest Postgres? I'd be surprised if David, of all people, has source code in a repo that won't run on Postgres.

Iren Patel Over a year ago

i am postgres 9.3 using

|

darkangel · Accepted Answer · 2015-11-12 07:34:22Z

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;

Collectives™ on Stack Overflow

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

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related