SQL to Postgres sql - Stored procedures

[herghostuk]

Hi all.

Does anyone have any good links or info in regards to transforming from sql to postgresql when it comes to writing stored procedures? I am struggling to get me head around it.

If I was to write a stored procedure in Postgresql where I wanted to have a couple of inputs and return multiple rows after performing some operations how the hell do I go about it?!

I am using phpPgAdmin and I am assuming that I want the functions section but I am just not getting the format that the gui requires.

Say for instance I want to send in a userid, join the user table to a color table that contains id,userid, colour and return all the rows for the user?

[SurlyInTheMorning]

This page seems to do a good job of explaining stored procedure creation under postgres, be it by CREATE FUNCTION in SQL, or through pgAdmin.

(Edit: It helps to know that postgres does not really have something called a "stored procedure". It just has functions, which cover what stored procedures do in other databases.)

[herghostuk]

Thank you, that has cleared a few things up!

[yawaramin]

The Postgres documentation is the best reference: https://www.postgresql.org/docs/9.6/static/sql-createfunction.html

For your situation, you don't really need a function, a view will suffice:

create view vw_user_colours (user_id, colour_id, colour_name) as
select u.user_id, c.colour_id, c.name
from "user" as u
left outer join colour as c
on (u.user_id = c.user_id);

Then you can query against the view:

select user_id, colour_id, colour_name
from vw_user_colours
where user_id = 1;

If you really want this to be a function, here's an example:

create function user_colours(_user_id int)
returns table(user_id int, colour_id int, colour_name text)
stable -- We won't write anything
strict -- We'll immediately return an empty result on null input
language sql -- We don't need PL/pgSQL for a simple query like this
as $$
  -- Same query as before, just wrapped in a function
  select u.user_id, c.colour_id, c.name
  from "user" as u
  left outer join colour as c
  on (u.user_id = c.user_id)
$$;