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).
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With