POSTGRESQL- Query has no destination for result data

Question

I'm new to postgres and to programming and I already searched for solution for this but I couldn't quite get it. I'm trying to make a function that will return information about all the customers on that particular country whenever I call the country. This is the error that pops up. I'm really sorry for asking this but I've been stuck here since yesterday.

ERROR: query has no destination for result data
HINT: If you want to discard the results of a SELECT, use PERFORM instead.
CONTEXT: PL/pgSQL function country(text) line 5 at SQL statement

Here is the function:

create or replace function country(text) returns text as $$
begin


  select customer_id, customer.first_name, customer.last_name 

  from customer 

  inner join address on customer.address_id = address.address_id 
  inner join city on address.city_id = city.city_id 
  inner join country on city.country_id = country.country_id 

  where country = '$1';
  end;
  $$

  language plpgsql;

You are querying, but not returning anything from the proc. How about using language sql as shown in the examples here: postgresql.org/docs/9.2/static/xfunc-sql.html — Glenn
– Glenn, Commented Dec 22, 2016 at 1:57
i changed it to sql and this pops up ERROR: syntax error at or near "select" LINE 5: select customer_id, customer.first_name, customer.last_name ^ im really confused — kimdasuncion12
– kimdasuncion12, Commented Dec 22, 2016 at 2:23
Look carefully at the examples. 35.4.4 has examples that match your scenario. You can try running them first, then changing to what you want. Notice the language sql version in the example does not have begin or end and has out parameters. — Glenn
– Glenn, Commented Dec 22, 2016 at 2:30

Community · Accepted Answer · 2017-05-23 10:30:50Z

If you are executing a select statement in a PL/pgSQL function, then you should place the result of the query in some variable(s) (= the destination). Then you work with the variable(s) in the function. You should also have a RETURN statement.

create or replace function country(text) returns text as $$
declare                   -- declare some variables
  id integer;
  fname text;
  lname text;
begin
  select customer_id, customer.first_name, customer.last_name 
    into id, fname, lname -- store query results in variables
  from customer 
  inner join address on customer.address_id = address.address_id 
  inner join city on address.city_id = city.city_id 
  inner join country on city.country_id = country.country_id 
  where country = $1;     -- don't quote parameter references

  -- do something with the variables and return a value from the function
  return format('%s: %s %s', id, upper(lname), fname);
end;
$$ language plpgsql;

Do note that the above only works if the query returns a single row. If the query returns multiple rows you can use a loop in the function. Even simpler, you can just return the results from the query like so:

create or replace function country(text)
returns table (id integer, first_name varchar, last_name varchar) as $$
begin
  return query
    select customer_id, customer.first_name, customer.last_name 
    from customer 
    inner join address on customer.address_id = address.address_id 
    inner join city on address.city_id = city.city_id 
    inner join country on city.country_id = country.country_id 
    where country = $1;
end;
$$ language plpgsql;

But like Evan Carroll said, unless you need a PL/pgSQL function to modify the data before returning it, you are better off with a simple view.

VishnuVS · Accepted Answer · 2021-11-02 07:11:36Z

1

There might be some situations where we want to call another psql function from a function. If we just want to invoke the function and don't assign the return value to anything, then using select inner_function_call(); inside the main function will throw this error. As the hint suggests, use perform inner_function_call() instead.

answered Nov 2, 2021 at 7:11

VishnuVS

1,1663 gold badges14 silver badges31 bronze badges

Comments

General Grievance · Accepted Answer · 2021-11-22 20:22:27Z

1

CREATE OR REPLACE FUNCTIOn func_process_client_contact(p_client_version_id bigint, p_publish_id bigint)
    RETURNS TABLE(status_message character varying)
    LANGUAGE plpgsql
AS $function$
declare 
    p_rowcount int;
    BEGIN
        -- validating input parameters : start
        IF ( p_client_version_id IS NULL OR p_publish_id IS NULL )
        THEN 
            RETURN;
        END IF;
        -- validating input parameters : end 

       WITH cte_target AS
       (
            SELECT  
            g.name AS clientname,
            gcr.group_id AS clientid,
            cr.id AS clientrelationid,
            crc.id AS clientrelationcontactid,
            crd.id AS clientrelationdesignation_id
            FROM GROUPS g
            JOIN group_client_relation gcr ON gcr.group_id =g.id
            JOIN client_relation cr ON cr.id = gcr.client_relation_id 
            JOIN client_relation_contact crc ON crc.client_relation_id =cr.id 
            JOIN client_relation_designation crd ON cr.client_relation_designation_id =crd.id
        )
        SELECT * FROM cte_target ct WHERE ct.clientname='ABC';
        GET DIAGNOSTICS p_rowcount = ROW_COUNT;
        RAISE NOTICE 'returned % rows', p_rowcount;
        IF ( p_rowcount=0 )
        THEN 
           RETURN query SELECT 'hello' AS status_message;
        ELSE
            RETURN query SELECT 'Success' AS status_message;
        END IF; 
    END
$function$

edited Nov 22, 2021 at 20:22

General Grievance

5,12039 gold badges40 silver badges60 bronze badges

answered Nov 22, 2021 at 13:26

neeru

111 bronze badge

1 Comment

Community Over a year ago

As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center.

Riya Bansal · Accepted Answer · 2016-12-22 04:48:17Z

0

Please use below to get result of given function..

create or replace function country(in_parameter text,out out_res refcursor) as $$
begin
open out_res for
select customer_id, customer.first_name, customer.last_name 

from customer 

inner join address on customer.address_id = address.address_id 
inner join city on address.city_id = city.city_id 
inner join country on city.country_id = country.country_id 

where country = '$1';
end;
$$

language plpgsql;

answered Dec 22, 2016 at 4:48

Riya Bansal

1,3111 gold badge11 silver badges10 bronze badges

2 Comments

kimdasuncion12 Over a year ago

hello Riya i tried it and it returned this result can you please help me?

kimdasuncion12 Over a year ago

country -------------------- <unnamed portal 1>

Evan Carroll · Accepted Answer · 2016-12-22 05:25:49Z

0

This is not normal for SQL. Normally, this would be a VIEW.

CREATE VIEW myView AS
    SELECT customer_id, customer.first_name, customer.last_name 
    FROM customer 
    INNER JOIN address USING (address_id)
    INNER JOIN city USING (city_id)
    INNER JOIN country USING (country_id);

Then you do

 SELECT * FROM myView WHERE country = ?

All of that said, if you insist on making this a function, and you shouldn't, you should make it a LANAGUAGE SQL and not LANGUAGE plppsql.

answered Dec 22, 2016 at 5:25

Evan Carroll

1

2 Comments

Muhammad Waheed Over a year ago

Can you please explain the impact of using sql' in language rather than plppsql`?

Evan Carroll Over a year ago

It's faster generally speaking. And only SQL can be inlined. It's also less complex and easier to maintain.

slow_learner · Accepted Answer · 2017-04-06 05:03:40Z

0

It worked for my colleague when she used OPEN MYCURS before the select query and RETURN MYCURS after the select query.

answered Apr 6, 2017 at 5:03

slow_learner

739 bronze badges

Collectives™ on Stack Overflow

POSTGRESQL- Query has no destination for result data

6 Answers 6

1 Comment

Comments

1 Comment

2 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

1 Comment

Comments

1 Comment

2 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related