Is it possible to retrieve tables with stored procedures?-CodePudding

It's the first time I use PostgreSQL and its most popular interface, Pgadmin4. I noticed I couldn't get tables using SELECT inside a stored procedure, but managed to do it with a function that returns a table. However, in MySQL it's possible to return queries using stored procedures, so I started wondering if I did anything wrong...

Thanks in advance.

Code I use in Mysql:

DELIMITER //
create procedure listar_ordenes_cat(in cat_name varchar(50)) 
begin
    select o.ordenid
    from ordenes o
    inner join detalle_ordenes d
    on o.ordenid = d.ordenid
    inner join productos pr
    on pr.productoid = d.productoid
    inner join categorias ca
    on ca.categoriaid = pr.categoriaid
    where ca.nombrecat = upper(cat_name);
end//

DELIMITER ;
call listar_ordenes_cat('Lacteos')

Script I had to use in Postgres:

create or replace function listar_ordenes_cat(in cat_name varchar(50)) 
returns table (ordenid int) as $$
begin
    return query
    select o.ordenid
    from ordenes o
    inner join detalle_ordenes d
    on o.ordenid = d.ordenid
    inner join productos pr
    on pr.productoid = d.productoid
    inner join categorias ca
    on ca.categoriaid = pr.categoriaid
    where ca.nombrecat = upper(cat_name);
end;

$$ language plpgsql


select * from listar_ordenes_cat('Lacteos')

CodePudding user response：

You get what you are looking for by defining a SQL function. It winds up essentially the same as your MySql.

create or replace function listar_ordenes_cat(in cat_name) 
  returns table (ordenid int) 
as $$

    select o.ordenid
      from ordenes o
     inner join detalle_ordenes d on o.ordenid = d.ordenid
     inner join productos pr      on pr.productoid = d.productoid
     inner join categorias ca     on ca.categoriaid = pr.categoriaid
     where ca.nombrecat = upper(cat_name);

$$ language sql;

But directly no you cannot get this from a stored procedure.