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, Mar 28 '17 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, Mar 28 '17 at 13:48
I suppose you should write `rech.primaryurn =urn` instead of `rech.primaryurn ='urn'` — etsa, Mar 28 '17 at 13:49

score 1 · Answer 1 · edited Jun 20 '20 at 09:12

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 '20 at 09:12

Community

1
1

answered Mar 28 '17 at 14:09

light souls

698
1
8
17

1

`return from`? (@_@) – Clodoaldo Neto Mar 28 '17 at 14:15
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](http://stackoverflow.com/questions/8169676/can-i-make-a-plpgsql-function-return-an-integer-without-using-a-variable) – light souls Mar 28 '17 at 14:26
I have never heard of `RETURN FROM` either. And don't found any [evidence](https://www.postgresql.org/docs/current/static/plpgsql-control-structures.html) that it's supposed to work. But [apparently, it does](http://rextester.com/LDAQA89851) (with 9.5 at least). – pozs Mar 28 '17 at 14:41
I tested in 9.6 and indeed it works. I wonder why isn't it in the docs. – Clodoaldo Neto Mar 28 '17 at 14:46
This is probably something like the assignment with `=` see [9.3](https://www.postgresql.org/docs/9.3/static/plpgsql-statements.html) vs. [9.4](https://www.postgresql.org/docs/9.4/static/plpgsql-statements.html): 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. – pozs Mar 28 '17 at 15:07
i changed query to @ErwinBrandstetter example.[return(select...)](http://stackoverflow.com/a/8169928/7689902) – light souls Mar 28 '17 at 15:24

score 0 · Answer 2 · answered Mar 28 '17 at 15:56

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;

PostgreSQL function does not return a value

2 Answers2