PostgreSQL Return affected rows by UPDATE/INSERT query

Question

OK. so I have these 2 queries. Both do the same thing:

1) If profile already exists then only update profile with new info 2) If profile does not exist then create a new profile

Restrictions: 1) username is unique (cannot be duplicate) 2) user_id is unique (cannot be duplicate)

I´m trying to get a boolean response from the server so my app can check if the query was effectively executed and rows were affected. For example if the user wants to change its username but the username he chose is already in use the query would not perform therefore no rows would be affected. I need some sort of return (count of affected rows?) or true/false telling me if a row was affected or not???

I am new to postgreSQL and I would greatly appreciate any form of help. Thanks =) Regards Jose Maria Landa =)

UPDATE people.profile SET profile_picture='test_profile_picture_url', alias='test_username' WHERE 

user_id='test_user_id'
AND NOT EXISTS (SELECT 1 FROM people.profile WHERE alias = 'test_username' AND user_id NOT LIKE 'test_user_id');

INSERT INTO people.profile (user_id, profile_picture, alias)
   SELECT 'test_user_id', 'test_profile_picture_url', 'test_username'
   WHERE NOT EXISTS (SELECT 1 FROM people.profile WHERE user_id = 'test_user_id' OR alias = 'test_username');

DO
$do$
BEGIN

IF EXISTS (SELECT 1 FROM people.profile WHERE user_id='test_user_id'
AND NOT EXISTS (SELECT 1 FROM people.profile WHERE alias = 'test_username' AND user_id NOT LIKE 'test_user_id')) 

THEN

-- UPDATE PROFILE INFO
UPDATE people.profile SET profile_picture='test_profile_picture_url', alias='test_username' WHERE 

user_id='test_user_id'
AND NOT EXISTS (SELECT 1 FROM people.profile WHERE alias = 'test_username' AND user_id NOT LIKE 'test_user_id');

ELSE 

-- CREATE NEW ACCOUNT
INSERT INTO people.profile (user_id, profile_picture, alias)
   SELECT 'test_user_id', 'test_profile_picture_url', 'test_username'
   WHERE NOT EXISTS (SELECT 1 FROM people.profile WHERE user_id = 'test_user_id' OR alias = 'test_username');

END IF;
END
$do$

Hambone · Accepted Answer · 2015-12-15 02:56:20Z

I would create a function and have it either return text or an enumeration with the results. Here is an example:

CREATE OR REPLACE FUNCTION people.update_profile(USERID text,
    USER_NAME text, PHOTO_URL text)
  RETURNS text AS
$BODY$
DECLARE
  rec profile;
  matching_uid boolean;
  matching_name boolean;
  matching_both boolean;
  result text;
BEGIN

  for rec in select * from people.profile where user_id = USERID or USER_NAME = alias
  loop
    if rec.user_id = USERID and rec.alias = USER_NAME then matching_both = true;
    elsif rec.user_id = USERID then matching_uid = true;
    elsif rec.alias = USER_NAME then matching_name = true;
    end if;
  end loop;

  if matching_both then
    update people.profile
    set profile_picture = PHOTO_URL
    where user_id = USERID;

    result := 'Photo Updated';    
  elsif matching_name then
    result := 'Username is in use.  Please choose another';
  elsif matching_uid then
    update people.profile
    set profile_picture = PHOTO_URL, alias = USER_NAME
    where user_id = USERID;

    result := 'Photo and User Name Updated';
  else
    insert into people.profile
    (user_id, alias, profile_picture)
    values (USERID, USER_NAME, PHOTO_URL);

    result := 'New User Added';
  end if;

  return result;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

If you call the function like this:

select people.update_profile('hamb', 'Hambone', 'http://purple.com');

It would give you back the message of whether it succesfully updated, and if so what actions were taken.

You definitely can return a rowcount of what's impacted with something like this following the insert/update:

GET DIAGNOSTICS rowcount = ROW_COUNT;

But given that the function will only return a 1 or a 0, I think either the text or an enumeration of those messages would be more valuable.

Thanks man!!! that did the trick all right!!! why didnt i think before??? you really helped me out. thanks again!!!

Collectives™ on Stack Overflow

PostgreSQL Return affected rows by UPDATE/INSERT query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related