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.
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')
