PostgreSQL function does not return a value

Question

-- I can not understand where the error

CREATE OR REPLACE FUNCTION get_person_membership (IN person_urn CHARACTER VARYING)

   RETURNS TEXT  AS
$BODY$

DECLARE
result text;
urn ALIAS FOR $1;

BEGIN

SELECT INTO result pers.mx_groupmember FROM mt_person AS pers, mxt_recordheader AS rech
WHERE rech.primaryurn = 'urn'
AND rech.entitytype = 'person' 
AND rech.logicalserverprefix = 'EA'
AND rech.id = pers.id;
RETURN result;

END;
$BODY$
LANGUAGE plpgsql
VOLATILE
COST 100

change SELECT INTO result pers.mx_groupmember to SELECT pers.mx_groupmember into result — Vao Tsun
– Vao Tsun, Commented Mar 28, 2017 at 13:44
also, you don't actually use the person_urn parameter in your query (neither its urn alias, which is completely unnecessary, because the parameter has a name already). — pozs
– pozs, Commented Mar 28, 2017 at 13:48
I suppose you should write rech.primaryurn =urn instead of rech.primaryurn ='urn' — etsa
– etsa, Commented Mar 28, 2017 at 13:49

Community · Accepted Answer · 2020-06-20 09:12:55Z

1

i simplified your query:

For input you can use $1, this goes to direct your condition.
You can return direct your result if you dont need anywhere else.

select * from get_person_membership('something');

CREATE OR REPLACE FUNCTION get_person_membership (IN person_urn CHARACTER VARYING) 
RETURNS TEXT  AS
$BODY$

BEGIN

RETURN (select pers.mx_groupmember --returns single value
FROM mt_person AS pers, mxt_recordheader AS rech
WHERE rech.primaryurn = $1 --input value from person_urn 
AND rech.entitytype = 'person' 
AND rech.logicalserverprefix = 'EA'
AND rech.id = pers.id);

END;
$BODY$
LANGUAGE plpgsql
VOLATILE
COST 100

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Mar 28, 2017 at 14:09

light souls

7381 gold badge9 silver badges18 bronze badges

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

6 Comments

Clodoaldo Neto Over a year ago

return from? (@_@)

light souls Over a year ago

same as

return (select pers.mx_groupmember  FROM mt_person AS pers, mxt_recordheader AS rech WHERE rech.primaryurn = $1 --input value from person_urn  AND rech.entitytype = 'person'  AND rech.logicalserverprefix = 'EA' AND rech.id = pers.id)

related link: first solution

pozs Over a year ago

I have never heard of RETURN FROM either. And don't found any evidence that it's supposed to work. But apparently, it does (with 9.5 at least).

Clodoaldo Neto Over a year ago

I tested in 9.6 and indeed it works. I wonder why isn't it in the docs.

pozs Over a year ago

This is probably something like the assignment with = see 9.3 vs. 9.4: it was supported a long ago (before 9.3), but was considered a hidden feature (and a bad design, because it can be easily confused with the equality test). This syntax seems to conform (at least) to PERFORM, so it might eventually get added to the docs. But until then, this is an undocumented feature, and as such, it might change without prior notice. Use this syntax very carefully.

|

user330315 · Accepted Answer · 2017-03-28 15:56:32Z

0

No need for PL/pgSQL, a simple SQL function will do:

CREATE OR REPLACE FUNCTION get_person_membership (IN person_urn CHARACTER VARYING)
   RETURNS TEXT  AS
$BODY$
  SELECT pers.mx_groupmember 
  FROM mt_person AS pers
    JOIN mxt_recordheader AS rech ON rech.id = pers.id
  WHERE rech.primaryurn = person_urn --<< input parameter
    AND rech.entitytype = 'person' 
    AND rech.logicalserverprefix = 'EA';
$BODY$
LANGUAGE sql;

answered Mar 28, 2017 at 15:56

user330315

Collectives™ on Stack Overflow

PostgreSQL function does not return a value

2 Answers 2

6 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related