Declare and return a json variable with postgresql function

Question

I'm trying to create a function PostgreSQL to check if a user exist in a table users by creating a function who return a JSON variable.

CREATE OR REPLACE FUNCTION login( uname character varying(55),pswd character varying(55)) 
RETURNS json AS
$$
DECLARE
  msg json ;
BEGIN
IF ((SELECT COUNT(*) FROM (SELECT * FROM users WHERE username=uname and password=pswd) AS row_count) =1)
THEN
msg="{ 'stat' : 'active' }";
    RETURN msg;
ELSE
msg="{ 'stat' : 'inactive' }";
    RETURN msg;
END IF;    
END;
$$ LANGUAGE plpgsql;

But when I try to using it it's return me the following error: ERROR: column "{ 'stat' : 'inactive' }" does not exist

user330315 · Accepted Answer · 2023-02-01 10:07:24Z

1

String constants need to be enclosed in single quotes in SQL (and PL/pgSQL). And JSON uses double quotes for keys and values, so { 'stat' : 'active' } is invalid JSON as well.

You need:

msg := '{"stat": "active"}';

The variable isn't really needed, you could also use:

return '{"stat": "active"}';

Note that the IF condition can be made a bit more efficient by using an EXISTS condition:

if exists (SELECT * FROM users WHERE username=uname and password=pswd) 
then 
  ...

answered Feb 1, 2023 at 10:07

user330315

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

Collectives™ on Stack Overflow

Declare and return a json variable with postgresql function

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related