What is the proper syntax for PostgreSQL stored procedures (functions)?

Question

I'm trying to write two types of stored procedures in PostgreSQL. From what I understand Postgre only has functions. I was wondering if someone can take a look at my code and offer pointers. Also, I'm am not familiar whether with the spacing/new lines of commands.

The first function needs to take input from user and add it onto a table. Suppose we have a table name "Car" with attributes "model" and "year". Will this be a correct stored function to add a new car to the table?

CREATE OR REPLACE FUNCTION
    addto_car(model IN Car.model%type, year IN Car.year%type)
RETURNS
    void
AS $$
BEGIN
    INSERT INTO Car VALUES(model, year);
END;
$$ LANGUAGE plpgsql; (#Is this correct? I'm using postgresql 9)

---------- Work in Progress code Function 1

CREATE OR REPLACE FUNCTION
    addto_car(In model Car.model%type, IN year Car.year%type)
AS $$
BEGIN
    INSERT INTO Car VALUES(model, year);
END;
$$ LANGUAGE plpgsql;

This now works! (inserts values model and year into Car).

your quotes don't agree. You start with && and end with $$. I'm pretty sure && is an invalid quote delimiter to begin with; you should start and end your function body with $$ or $SOMETHING$ . — SingleNegationElimination
– SingleNegationElimination, Commented Aug 10, 2011 at 15:58

Adriano Carneiro · Accepted Answer · 2011-08-10 15:55:49Z

5

From the Official Documentation

CREATE [ OR REPLACE ] FUNCTION
    name ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ] [, ...] ] )
    [ RETURNS rettype
      | RETURNS TABLE ( column_name column_type [, ...] ) ]
  { LANGUAGE lang_name
    | WINDOW
    | IMMUTABLE | STABLE | VOLATILE
    | CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT
    | [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER
    | COST execution_cost
    | ROWS result_rows
    | SET configuration_parameter { TO value | = value | FROM CURRENT }
    | AS 'definition'
    | AS 'obj_file', 'link_symbol'
  } ...
    [ WITH ( attribute [, ...] ) ]

You will find your answer there and, maybe, learn two or three useful things on the process.

You might be particularly interested in the RETURNS TABLE construct.

answered Aug 10, 2011 at 15:55

Adriano Carneiro

58.8k12 gold badges94 silver badges123 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

tvguide1234 Over a year ago

Hmm I'm having difficulty understanding all of the commands. Taking a look at my first function, for 'argtype' should I put 'IN' since I want to input values entered by user into the database?

CREATE OR REPLACE FUCTION     addto_car(IN model Car.model%type, IN year Car.year%type) RETURNS     void AS $$ BEGIN     INSERT INTO Car VALUES(model, year); END; $$ LANGUAGE plpgsql;

tvguide1234 Over a year ago

I don't necessarily want it to return the table, I just want it to insert the new tuple so I'm not sure if I should use "RETURN TABLE" or "RETURN VOID"

Adriano Carneiro Over a year ago

Don't even use RETURN then. If you have problem figuring out the doc (that's understandable), take a look at the examples. Some of them don't even have the RETURN clause.

tvguide1234 Over a year ago

Ah I see. I'm going to make a new version so to show a 'work in progress' version of my code.

tvguide1234 Over a year ago

OK I understand how to do function 1. For function 2 can I use RETURNS record?

Collectives™ on Stack Overflow

What is the proper syntax for PostgreSQL stored procedures (functions)?

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related