1

I'm looking for a way to have a function or procedure in Postgresql 12, which can be called with a single line for usage in commandline.

1) Perform a query that takes a parameter as input

For example:
select id from table1 where name = $PARAMETER

2) Based on the ids returned, execute multiple deletes

For example:

delete from table2 where id = $id
delete from table3 where id = $id

In SQL Server I would use a stored procedure for this, but I'm lost on how how to do this in PostgreSQL.

William Robertson
  • 15,273
  • 4
  • 38
  • 44
Bart Schelstraete
  • 11
  • 3
  • Postgres also has the concept of stored procedures and functions. If you can write them in SQL Server you shouldn't have much problem adapting to the plpgsql syntax. – gsteiner May 15 '20 at 17:26
  • @steiner: that is completely incorrect. That's completely different compared to sql server. – Bart Schelstraete May 15 '20 at 18:09
  • I'm not saying the syntax is exactly the same. I'm saying with some research and reading you should be able to adapt it. – gsteiner May 18 '20 at 19:37

1 Answers1

1

If your origin is on MS SQL, then good start is reading documentation. The language and environment is different (maybe very different to what you know).

But your task is simple - you can use procedures (from Postgres 11) or functions:

CREATE OR REPLACE FUNCTION s(_name text)
RETURNS void AS $$
DECLARE _id int;
BEGIN
  SELECT id FROM table1 WHERE name = _name INTO _id;
  DELETE FROM table2 WHERE id = _id;
  DELETE FROM table2 WHERE id = _id;
END
$$ LANGUAGE plpgsql;

or like procedure

CREATE OR REPLACE PROCEDURE s(_name text)
AS $$
DECLARE _id int;
BEGIN
  SELECT id FROM table1 WHERE name = _name INTO _id;
  DELETE FROM table2 WHERE id = _id;
  DELETE FROM table2 WHERE id = _id;
END
$$ LANGUAGE plpgsql;

Function is executed via SELECT s('somename')

Procedure is executed by statement CALL s('somename')

Pavel Stehule
  • 42,331
  • 5
  • 91
  • 94