Postgresql say “schema does not exist”

Question

How can I make this query works?

I've the function top_movies_ceiling(3)

CREATE OR REPLACE FUNCTION top_movies_ceiling(n_top integer)  -- n_top dos mais vendidos
RETURNS SETOF inventory AS $$
BEGIN
RETURN QUERY
SELECT *
FROM inventory
ORDER BY sales DESC
LIMIT n_top;
END;
$$LANGUAGE plpgsql;

that returns:

prod_id|quan_in_stock|sales

Now, when I run the query:

SELECT products.price 
FROM products
WHERE products.prod_id = prod_id.top_movies_ceiling(3);

I get the error: schema "prod_id" does not exist

I hope that you can help me! Thanks!

Because I've opened a Cursor and i've been fetch it into a numeric variable from the same type that "prod_id", for the purpose to save it. — Jorge Mendes
– Jorge Mendes, Commented Oct 13, 2017 at 9:07

Vao Tsun · Accepted Answer · 2017-10-13 09:05:43Z

2

a wild guess - is it what you try to do?

SELECT products.price 
FROM products
JOIN (select * from top_movies_ceiling(3)) top_m on top_m.prod_id = products.prod_id
;

answered Oct 13, 2017 at 9:05

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

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

Comments

OBi · Accepted Answer · 2017-10-13 09:09:12Z

1

The error is in:

prod_id.top_movies_ceiling(3)

that mean: function top_movies_ceiling(int) from schema prod_id.

Please enter the CREATE FUNCTION of top_movies_ceiling() for better understand what you want.

answered Oct 13, 2017 at 9:09

OBi

1161 silver badge4 bronze badges

Collectives™ on Stack Overflow

Postgresql say “schema does not exist”

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related