1

I am trying to create a procedure with a wildcard with postgresql unfortunately I do not have any success. It looks like the postgresql versions have different ways of creating procedures, however, I find more help on SQL Server. Here the simple procedure I am trying to create:

create procedure getpeople (title varchar, city varchar)
LANGUAGE plpgsql 
as $$
select * from salesperson where job_title=title and city like % city

$$;

I am using postgreSQL12.1not

Herc01
  • 610
  • 1
  • 8
  • 17
  • 1
    What do you want this procedure to do? Procedures do not return data, so I would guess you actually want a function? – Jeremy May 16 '21 at 01:22

2 Answers2

1

You must write the result type. And the language is sql not plpgsql.

This works:

create table salesperson(
    name text,
    job_title text,
    city text
);

create function getpeople (title varchar, city varchar) 
  returns salesperson
LANGUAGE sql 
as $$
select * from salesperson where job_title=title and city like '%'||city
$$;

insert into salesperson values ('Boby', 'bos', 'ny');

select getpeople('bos','ny');
Emilio Platzer
  • 2,327
  • 21
  • 29
0

In addition to the answer by @Emilio Platzer: If you want to get multiple lines out of your function (not procedure) using PL/pgSQL, you can set it to return a table:

CREATE OR REPLACE FUNCTION getpeople (varchar,varchar) 
RETURNS TABLE (name text, job_title text, city text)
AS $$
BEGIN
RETURN QUERY 
 (SELECT * FROM salesperson sp
  WHERE sp.job_title=$1 AND sp.city LIKE $2||'%');
END  
$$  LANGUAGE plpgsql;

.. and call it like this:

SELECT * FROM getpeople('ceo','D');

Demo: db<>fiddle

Jim Jones
  • 18,404
  • 3
  • 35
  • 44
  • thanks, this is exactly I am looking for. But can you tell me how you are mapping the columns please? what are $1, $2?what would $0 represent? – Herc01 May 16 '21 at 10:18
  • @Herc01 $1 and $2 corresponds to the `varchar` parameters used to call the function `getpeople (varchar,varchar)`. $0 does not exist :) – Jim Jones May 16 '21 at 10:23