I am getting the error subquery must return only one column when I try to run the following query:

Question

CREATE or REPLACE PROCEDURE checkUpdateAdd(imei1 inout text, assetName1 inout text)
language 'plpgsql'
AS $BODY$
declare
begin
    PERFORM * from msdata;
if (select * from msdata where imei = imei1) then
    --UPDATE "public"."msdata"
    UPDATE public.msdata SET assetname1 = assetname where imei = imei1;
    -- return assetname;
-- SELECT * FROM msdata ORDER BY imei ASC;
elseif (select * from msdata where imei != imei1) then
    Insert into public.msdata(imei,assetname) values (imei1,assetname1);
    --return;
end if;
    -- return (null,null);
end;
$BODY$;

call checkUpdateAdd('123','abc1');
SELECT * FROM msdata;

where msdata is an existing table with columns imei and assetname.

So now if I have to create a procedure which on calling - creates or updates the msdata

Can you pls. explain what do you want to achieve? Is it that if imei1 exists in the table then return the value associated with it, otherwise create a new record (imei1, assetname1)? Or update assetname if imei1 if exists? — Stefanov.sm, Oct 20 '21 at 13:36
imei and assetnumber are the coloumns in table - ms data I want to create query which - has 2 conditions - 1st being ---> if imei = imei1 then asset number changes 2nd being ---> if imei =! imei1 then new row gets created with new imei and asset number — codeLibrary, Oct 20 '21 at 13:48

Stefanov.sm · Answer 1 · 2021-10-20T20:25:07.043

0

I would rather create a function that updates or inserts and returns the old value of assetname field upon update or null (as there is no old value) upon insert. Please note the FOUND predefined variable.

CREATE or REPLACE FUNCTION checkUpdateAdd(imei1 text, assetName1 text)
returns text language plpgsql AS
$BODY$
declare
  retval text;
begin
  select assetname into retval from msdata where imei = imei1;
  if FOUND then -- (imei = imei1) exists, return the old assetname value
    update msdata SET assetname = assetname1 where imei = imei1;
    return retval;
  else
    insert into msdata(imei, assetname) values (imei1, assetname1);
    return null;
  end if;
end;
$BODY$;

Btw. if imei is a primary key or if it has an unique constraint then the task becomes much more simple. You ca use insert on conflict do update and do not need a function or procedure.

Update As a procedure:

CREATE or REPLACE PROCEDURE checkUpdateAdd(imei1 text, assetName1 text) 
language plpgsql AS
$BODY$
begin
  if exists (select from msdata where imei = imei1) then
    update msdata SET assetname = assetname1 where imei = imei1;
  else
    insert into msdata(imei, assetname) values (imei1, assetname1);
  end if;
end;
$BODY$;

edited Oct 20 '21 at 20:25

answered Oct 20 '21 at 13:47

Stefanov.sm

11,215
2
21
21

Thank u so much. but what is the major reason behind not choosing a PROCEDURE? – codeLibrary Oct 20 '21 at 14:02
A function can be used in expressions. Well, more or less it's a matter of personal preference too. Do you accept the answer? – Stefanov.sm Oct 20 '21 at 14:04
I will have to add a procedure or a function because we need the logic initially so that later we can upload.csv files and this function can be used – codeLibrary Oct 20 '21 at 14:04
Good point, it makes sense to encapsulate & reuse. – Stefanov.sm Oct 20 '21 at 14:06
Well I completely agree with you about personal preference, but it is said that function has to return but procedure doesn't. I was wondering how can it be implemented in procedure. – codeLibrary Oct 20 '21 at 19:22
Well at a very basic level procedures are routines that do things, functions are routines that get things. If you have a piece of code that does both (out or in out parameters) then ask your self "Is this piece of code doing multiple thing, if so can/should it be separated into multiple routines of code"? – Belayer Oct 20 '21 at 22:40

I am getting the error subquery must return only one column when I try to run the following query:

1 Answers1