PostgreSQL Stored Procedures
Learn how to store SQL functions in your CARTO database.
What is a stored procedure?
PostgreSQL allows you to extend the database functionality with user-defined functions by using various procedural languages, which are often referred to as stored procedures.
With stored procedures you can create your own custom functions and reuse them in applications or as part of other database’s workflow.
Basic structure of a PL/pgSQL function
CREATE FUNCTION function_name(argument1 type,argument2 type)
RETURNS type AS
BEGIN
staments;
END;
LANGUAGE 'language_name';
Where:
- We specify the name of function followed by the
CREATE FUNCTIONclause. - Provide a list of parameters inside the paretheses, also specifying each data type (integer, boolean, geometry, etc..)
RETURNSspecifies the return type of the function.- Place the block of code inside inside the
BEGINandEND;. - Indicate the procedural language of the function. For PostgreSQL is usually
plpgsql. -
The conditions can be
STRICT,IMMUTABLEorVOLATILE.STRICT: writeSTRICTwhen the input arguments haveNULLvalues. The function is not evaluated and returns aNULLvalue.IMMUTABLE: The function ensures the same result (it caches it) if the same value is put as an input arguments. This function can’t change the database.VOLATILE: opposite ofIMMUTABLE. The function has a result that changes the result even when you write the same input values.
To call the created function you could use:
SELECT * FROM function_name(val1,val2);
if the function returns a table, or just
SELECT function_name(val1,val2);
If one of the variables is taken from a column in a table:
SELECT function_name(val1,val2) FROM table;
Input and output arguments
In the previous section we defined a function like
CREATE FUNCTION function_name(argument1 type,argument2 type)
RETURNS type AS
BEGIN
staments;
END;
LANGUAGE language_name;
Where we define the input arguments and their type and then we define the type of the output argument within the RETURNS statement.
However, there is another way to define the input and output arguments with IN and OUT, where IN define the input arguments and OUT the
output arguments. The OUT argument declaration is useful when you need to return multiple values. For example:
CREATE FUNCTION sum_n_product(IN x int,IN y int, OUT sum int, OUT prod int) AS $$
BEGIN
sum := x + y;
prod := x * y;
END;
$$ LANGUAGE plpgsql;
Then the function can be called as: SELECT * FROM sum_n_product(1,2), where you only need to pass the IN arguments to the function.
Notice that if we use IN and OUT arguments, we don’t use the RETURNS statement in the function definition. Also, if you define the arguments with IN or OUT, there is no need to put both. You can define IN arguments without the need to put OUT arguments or viceversa.
On the other hand, there are different ways to refer to the input arguments within the function.
- If the input arguments have no names:
CREATE FUNCTION function_name(type,type)
RETURNS type AS
BEGIN
v1:= abs($1); -- $1 refers to the first input argument (reading from left to right)
v2:= $2; -- $2 refers to the second input argument (reading from left to right)
END;
LANGUAGE 'plpgsql';
- Using aliases for the input arguments.
We can assign alias to the input arguments.
CREATE FUNCTION function_name(type,type)
RETURNS type AS
DECLARE
val1 alias for $1;
val2 alias for $2;
BEGIN
v1:= abs($1); -- $1 refers to the first input argument (reading from left to right)
v2:= $2; -- $2 refers to the second input argument (reading from left to right)
END;
LANGUAGE 'plpgsql';
Variables and constants
A PL/pgSQL variable holds a values that can be changed through the function. A variables is always associated to a data type. Before you use a variable we must declare it in the proper section, using the following syntax:
CREATE FUNCTION function_name(argument1 type,argument2 type)
RETURNS type AS
DECLARE
variable_name data_type [:= value];
BEGIN
staments;
END;
LANGUAGE language_name;
Constants can’t be changed through the function. It is declared in a very similar way than variables.
CREATE FUNCTION function_name(argument1 type,argument2 type)
RETURNS type AS
DECLARE
variable_name CONSTANT data_type [:= value];
BEGIN
staments;
END;
LANGUAGE language_name;
Errors and messages
To raise a message, you use the RAISE statement: RAISE level format; the level option specifies the error severity. There are the following levels:
- DEBUG
- LOG
- NOTICE
- INFO
- WARNING
- EXCEPTION
For example:
RAISE INFO 'information message %', now() ;
RAISE LOG 'log message %', now();
RAISE DEBUG 'debug message %', now();
RAISE WARNING 'warning message %', now();
RAISE NOTICE 'notice message %', now();
However, the only error that would raise in the CartoDB Editor is RAISE EXCEPTION.
For example:
CREATE OR REPLACE FUNCTION sum_n_product3(IN x int,IN y int, OUT sum int, OUT prod int) AS $$
BEGIN
IF x < 2 THEN
RAISE EXCEPTION 'information message %', now();
END IF;
sum := x + y;
prod := x * y;
END;
$$ LANGUAGE plpgsql
If we call this function wihtin the CartoDB Editor, we will get the message of the RAISE EXCEPTION.
On the other hand, the message errors RAISE NOTICE, RAISE WARNING and RAISE INFO only will appear if you use the SQL API outsite the editor. If we modify the previous function to add those error messages:
CREATE OR REPLACE FUNCTION sum_n_product3(IN x int,IN y int, OUT sum int, OUT prod int) AS $$
BEGIN
IF x < 2 THEN
RAISE WARNING 'information message %', now();
RAISE NOTICE 'information message %', now();
RAISE INFO 'information message %', now();
END IF;
sum := x + y;
prod := x * y;
END;
$$ LANGUAGE plpgsql
And then we call the function using the SQL API as a URL:
https://username.carto.com/api/v2/sql?q=SELECT * FROM sum_n_product3(1,2)&api_key=API_KEY
We get the next response with the three error messages:
{
rows: [
{
sum: 3,
prod: 2
}
],
time: 0.001,
fields: {
sum: {
type: "number"
},
prod: {
type: "number"
}
},
total_rows: 1,
warnings: [
"information message 2016-06-08 09:43:33.923025+00"
],
notices: [
"information message 2016-06-08 09:43:33.923025+00"
],
infos: [
"information message 2016-06-08 09:43:33.923025+00"
]
}
The error RAISE LOG and RAISE DEBUG error messages are not returned in the client side, that’s because those messages are not showed to the client by default, they are displayed in the server side. The configuration of these error messages are controlled by the client_min_messages and log_min_messages configuration parameters. (more information here)
If the level is not specified, the defaults level will be EXCEPTION that raises an error and stops the current transaction.
A transaction is a unit of work that is performed against a database. Transactions are units or sequences of work accomplished in a logical order, whether in a manual fashion by a user or automatically by some sort of a database program. A transaction is the propagation of one or more changes to the database. For example, if you are creating or updating or deleting a record from the table, then you are performing transaction on the table. It is important to control transactions to ensure data integrity and to handle database errors.
Dynamic PL/pgSQL functions
In order to write dynamic PL/pgSQL queries the EXECUTE 'sql'; is used. The EXECUTE coommand it is used to generate dynamic commands inside the PL/pgSQL function. EXECUTE command it is used when the function involves different tables or different data types each time that the function is executed.
Syntax of EXECUTE command:
EXECUTE command-string [ INTO [STRICT] target ] [ USING expression [, ... ] ];
command-stringis an expression that contains que query to be executed.- The
targetstatement is optional. Is a record variable, row variable or a comma-separated list of simple variables and record/row fields, into which the results of the command will be stored. - The
usingstatement is optional. Supply values to be inserted into the command.
Features:
- There is no plan caching for commands executed via EXECUTE. The command is always planned each time the statements are running.
- Any required variable values must be inserted in the command string as it is constructed.
- The INTO clause specifies where the results of a SQL command returning rows should be assigned. If a row or variable list is provided, it must exactly match the structure of the query’s results (when a record variable is used, it will configure itself to match the result structure automatically). If multiple rows are returned, only the first will be assigned to the INTO variable. If no rows are returned, NULL is assigned to the INTO variable(s). If no INTO clause is specified, the query results are discarded.
- If the STRICT option is given, an error is reported unless the query produces exactly one row.
For more information and examples look at this section of the PostgreSQL documentation.
Functions that doesn’t return any row
If we create a function that doesn’t return any row, but performs a writing operation, such as an UPDATE, INSERT or DELETE, we need to set the RETURNS TYPE statement as RETURNS void
For example:
CREATE OR REPLACE FUNCTION logfunc1(logtxt text) RETURNS void AS $$
BEGIN
INSERT INTO untitled_table_1 (description,currenttime) VALUES (logtxt,'now');
END;
$$ LANGUAGE plpgsql
We could call this function with SELECT logfunc1('coolestWordEver') or SELECT * FROM logfunc1('someCoolWord') and postgreSQL will return no rows, but if you go to your table you will see that there is a new row with values in description and currentime columns.
You can apply the RETURNS void to UPDATE OR DELETE a row.
For example, this function when it’s called, updates the the description column that has the cartodb_id that you put as an argument:
CREATE OR REPLACE FUNCTION upfunc(logtxt text, id numeric) RETURNS void AS $$
BEGIN
UPDATE coooltable
SET description = logtxt
WHERE cartodb_id = id;
END;
$$ LANGUAGE plpgsql
And in this other example, the function will delete the row that have the cartodb_id that you put as an input.
CREATE OR REPLACE FUNCTION delfunc(id numeric) RETURNS void AS $$
BEGIN
DELETE FROM coooltable
WHERE cartodb_id = id;
END;
$$ LANGUAGE plpgsql
Function that returns a table
To return a table from a PL/pgSQL function, we have to set RETURNS TABLE (column1 type, column2 type, etc) where column1, column2 are the names of the columns that will be returned.
This example returns a frequency table from a specific column and specific table:
CREATE OR REPLACE FUNCTION get_freq_table()
RETURNS TABLE(
name VARCHAR,
frequency INT,
total_frequency INT,
relative_frequency NUMERIC,
sum_relative_frequency NUMERIC
)
AS $$
BEGIN
RETURN QUERY
WITH a as (
SELECT DISTINCT adm0_a3, count(*) OVER (PARTITION BY adm0_a3) as freq,
count(*) OVER() as total_freq
FROM populated_places_spf
),
b as(
SELECT a.adm0_a3, a.freq, a.total_freq,
round(
(a.freq::numeric/a.total_freq::numeric),
2
) as relative_freq
FROM a
)
SELECT b.adm0_a3::VARCHAR,
b.freq::INT,
b.total_freq::INT,
b.relative_freq::NUMERIC,
sum(b.relative_freq)
OVER(ORDER BY b.relative_freq)::NUMERIC as suma_rel_freq
FROM b;
END;
$$ LANGUAGE 'plpgsql'
We could also modify the function to return a frequency table with a dynamic function:
CREATE OR REPLACE FUNCTION get_freq_table_general(col VARCHAR, tablName VARCHAR)
RETURNS TABLE(
name VARCHAR,
frequency INT,
total_frequency INT,
relative_frequency NUMERIC,
sum_relative_frequency NUMERIC
)
AS $$
BEGIN
RETURN QUERY EXECUTE '
WITH a as (
SELECT DISTINCT '|| col || ' as colName, count(*) OVER (PARTITION BY '||
col ||') as freq , count(*) OVER() as total_freq FROM '|| tablName ||'
),
b as(
SELECT a.colName,a.freq, a.total_freq,
round(
(a.freq::numeric/a.total_freq::numeric),
2::integer
) as relative_freq
FROM a)
SELECT b.colName::VARCHAR, b.freq::INT, b.total_freq::INT,
b.relative_freq::NUMERIC,
sum(b.relative_freq)
OVER(ORDER BY b.relative_freq)::NUMERIC as suma_rel_freq
FROM b';
END;
$$ LANGUAGE 'plpgsql'
Functions with Security Definer
In order to allow CARTO anonymous user (called publicuser) to perform writing operations such as INSERT or UPDATE queries, we need to define those actions inside a stored procedure. That function will afterwards be granted execution permission, so it will run with the same permission as the account owner.
This is an example of function that can be called to insert new points and alphanumerical data in a table:
CREATE OR REPLACE FUNCTION insertpoint(
lon numeric,
lat numeric,
name text,
description text,
category text,
tablename text
)
RETURNS TABLE(cartodb_id INT)
LANGUAGE 'plpgsql' SECURITY DEFINER
RETURNS NULL ON NULL INPUT
AS $$
DECLARE
sql text;
BEGIN
sql:= 'WITH do_insert AS (
INSERT INTO '||quote_ident(tablename)||'(the_geom, name, description, category)
VALUES '
|| '('
|| 'ST_SetSRID(ST_MakePoint('||lon||','||lat||'), 4326),'
|| quote_literal(name)||','
|| quote_literal(description)||','
|| quote_literal(category)
|| ')'
||'RETURNING cartodb_id)'
||'SELECT cartodb_id FROM do_insert';
RETURN QUERY EXECUTE sql;
END;
$$;
--Grant access to the public user
GRANT EXECUTE ON
FUNCTION insertpoint(numeric, numeric, text, text, text, text)
TO publicuser;
Click here to find more detailed instructions for creating a Security Definer function that allows to collect points from a public map in a secure way
Trigger functions
The trigger functions have three main properties:
- They are immutable
- The
RETURNSstatement returns a trigger type - They don’t take any input argument
The trigger functions can be defines in two setps:
1- Define the trigger function:
CREATE OR REPLACE FUNCTION tr_function()
RETURNS TRIGGER
AS $$
BEGIN
NEW.c3 = NEW.c1 + NEW.c2
RETURN NEW
END;
$$
LANGUAGE 'plpgsql';
Where NEW is a variable that contains a row of the table.
2- Define when the trigger will be fired:
CREATE TRIGGER tr
BEFORE INSERT ON tableName
FOR EACH ROW EXECUTE
PROCEDURE tr_function();
Where:
- BEFORE (or AFTER): define when the trigger function will be activated (before or after the
INSERToperation) - FOR EACH ROW: the operation of the trigger function is executed once for each row.
- FOR EACH STATEMENT: the operation of the trigger function is executed once for each statement.
If we want to remove our triggers:
DROP TRIGGER triggerName ON tableName
or
DROP TRIGGER IF EXISTS triggerName ON tableName
Control structures
IF statement
The IF statement it is used to execute a command conditionally. PL/pgSQL has 3 ways to use the IF statements.
- IF THEN
IF condition THEN statement; END IF;The condition is a boolean expression that has to be evaluated as
trueorfalse, the statement will only be executed if the condition istrue. - IF THEN/ELSE
IF condition THEN statements; ELSE alternative-statements; END IF;The
IF THEN/ELSEstatement executes a command when the condition istrueand it executes an alternative command when the condition isfalse. - IF THEN/ ELSIF THEN/ELSE
IF condition-1 THEN
if-statement;
ELSIF condition-2 THEN
elsif-statement-2
...
ELSIF condition-n THEN
elsif-statement-n;
ELSE
else-statement;
END IF;
The IF THEN/ ELSIF THEN/ELSE statement allows you to have multiple conditions to evaluate. In case one condition is true, PostgreSQL will stop evaluating the underneath conditions.
CASE statement
This is similar to IF statement, but the CASE operator allows you to execute statements based on conditions. There are two ways to apply a CASE.
- Simple CASE statement
CASE search-expression WHEN expression_1 [, expression_2, ...] THEN when-statements [ ... ] [ELSE else-statements ] END CASE;The search-expression is an expression that will be evaluated against the expression in each
WHENbranch using equality operand (=). If a match is found, thewhen-statementsin the correspondingWHENbranch are executed. The subsequent expressions underneath will not be evaluated.
Example:
CREATE OR REPLACE FUNCTION get_price_segment(p_film_id integer)
RETURNS VARCHAR(50) AS $$
DECLARE
rate NUMERIC;
price_segment VARCHAR(50);
BEGIN
-- get the rate based on film_id
SELECT INTO rate rental_rate
FROM film
WHERE film_id = p_film_id;
CASE rate
WHEN 0.99 THEN price_segment = 'Mass';
WHEN 2.99 THEN price_segment = 'Mainstream';
WHEN 4.99 THEN price_segment = 'High End';
ELSE price_segment = 'Unspecified';
END CASE;
RETURN price_segment;
END; $$
LANGUAGE plpgsql;
- Searched CASE statement
CASE
WHEN boolean-expression-1 THEN statements
[ WHEN boolean-expression-2 THEN statements ... ]
[ ELSE statements ]
END CASE;
The searched CASE statement executes statements based on the result of Boolean expressions in each WHEN clause.
LOOP statement
This is used to execute a block of statements repeatedly until a condition becomes true. The basic syntax is:
LOOP
Statements;
EXIT [<<label>>] WHEN condition;
END LOOP;
Example:
CREATE OR REPLACE FUNCTION fibonacci (n INTEGER)
RETURNS INTEGER AS $$
DECLARE
counter INTEGER := 0 ;
i INTEGER := 0 ;
j INTEGER := 1 ;
BEGIN
IF (n < 1) THEN
RETURN 0 ;
END IF;
LOOP
EXIT WHEN counter = n ;
counter := counter + 1 ;
SELECT j, i + j INTO i, j ;
END LOOP ;
RETURN i ;
END ;
$$ LANGUAGE plpgsql;
The Fibonacci function accepts an integer and returns the Nth Fibonacci number. By definition, Fibonacci numbers are sequence of integers starting with 0 and 1, and each subsequent number is the product the previous two numbers, for example 1, 1, 2 (1+1), 3 (2+1), 5 (3 +2), 8 (5+3), …
In the declaration section, the counter variable is initialized to zero (0). Inside the loop, when counter equals n, the loop exits. The statement:
SELECT j, i + j INTO i, j ;
Swaps i and j at the same time without using a temporary variable.
WHILE loops
The WHILE loop statement executes a block of statements until a condition evaluates to false. In the WHILE loop statement, PostgreSQL evaluates the condition before executing the block of statements. If the condition is true, the block of statements is executed until it is evaluated to false.
CREATE OR REPLACE FUNCTION fibonacci (n INTEGER)
RETURNS INTEGER AS $$
DECLARE
counter INTEGER := 0 ;
i INTEGER := 0 ;
j INTEGER := 1 ;
BEGIN
IF (n < 1) THEN
RETURN 0 ;
END IF;
WHILE counter <= n LOOP
counter := counter + 1 ;
SELECT j, i + j INTO i, j ;
END LOOP ;
RETURN i ;
END ;
FOR loops
FORloop for looping through a ranges of integersFOR loop_counter IN [ REVERSE ] from.. to [ BY expression ] LOOP statements END LOOP [ label ];
First, PostgreSQL creates a integer variable loop_counter that exists only inside the loop. By default, the loop counter is added after each iteration, If you use the REVERSE keyword, PostgreSQL will subtract the loop counter.
Second, the FROM and TO are expressions that specify the lower and upper bounds of the range. PostgreSQL evaluates those expressions before entering the loop.
Third, the expression following the BY clause specifies the iteration step. If you omit this, the default step is 1. PostgreSQL also evaluates this expression once on loop entry
FORloop for looping through a query result
FOR target IN query LOOP
statements
END LOOP [ label ];
FORloop for looping through a query result of a dynamic query
FOR row IN EXECUTE string_expression [ USING query_param [, ... ] ]
LOOP
statements
END LOOP [ label ];
Pl/pgSQL and PostGIS
It’s possible to create Pl/pgSQL functions with PostGIS to predefine a process to modify the geometries of some data. For example, based on this example that creates star polygons from points (read it !! it’s detailed and fun), we can create a function like this one:
CREATE OR REPLACE FUNCTION regularPolygons(
IN center geometry,
IN radius numeric,
IN number_of_sides integer
) RETURNS GEOMETRY AS
$$
DECLARE
-- the geometry as WKT
geometry_wkt text := '';
-- a loop counter
i integer := 0;
-- angle to rotate
angle numeric;
-- reference line to rotate around
ref_line geometry;
BEGIN
-- create reference line to rotate the points
-- it creates a 12 o'clock lines from the center
ref_line = ST_MakeLine(
center,
ST_Transform(
ST_SetSRID(
ST_MakePoint(ST_X(center),ST_Y(center) + radius),
3857)
,4326));
-- calculate angle depending ong number of sides of the
angle = radians(360::numeric/number_of_sides::numeric);
-- Start loop to create polygon using the ref_line
-- and the declared variable i
WHILE (i < number_of_sides) LOOP
geometry_wkt = geometry_wkt ||
(ST_X(ST_EndPoint(ST_RotateZ(ref_line, angle * i))) +
ST_X(center)) || ' ' ||
(ST_Y(ST_EndPoint(ST_RotateZ(ref_line, angle * i))) +
ST_Y(center)) || ',';
-- increment counter
i = i + 1;
END LOOP;
-- add the first point again so the linestring is closed
geometry_wkt = geometry_wkt ||
(ST_X(ST_EndPoint(ref_line)) + ST_X(center)) || ' ' ||
(ST_Y(ST_EndPoint(ref_line)) + ST_Y(center));
-- Transform pair of points into a polygon geometry
geometry_wkt = 'POLYGON(('|| geometry_wkt ||'))';
RETURN geometry_wkt::geometry;
END;
$$ LANGUAGE plpgsql;
The regularPolygons() function takes three input arguments: the points that you want to use as a center of the output polygons, length of the radius and number of sides of the output polygon.
Using this function like this
SELECT regularPolygons(the_geom,100000,6), adm0_a3
FROM populated_places_spf
WHERE adm0_a3 = 'PRT' UNION ALL
SELECT regularPolygons(the_geom,100000,4), adm0_a3
FROM populated_places_spf
WHERE adm0_a3 = 'ESP' UNION ALL
SELECT regularPolygons(the_geom,100000,3), adm0_a2
FROM populated_places_spf
WHERE adm0_a3 = 'FRA'
We can create a different way to classify our point data like in this example.
Exercise
Create a stored procedure + trigger workflow to store changes from any of your tables to a log table
First, we need to create the log table and cartodbfy it.
CREATE TABLE version_control();
SELECT CDB_CartodbfyTable('username','version_control');
Cartodbfication will add the_geom, `the_geom_webmercator’ and ‘cartodb_id’ columns to the table, as well as some triggers to populate these columns automatically when a new row is added (we’ll see more about this at the end of the exercise).
Next step would be adding some extra columns to the log table.
ALTER TABLE version_control ADD COLUMN data json; -- to store the complete row in JSON format
ALTER TABLE version_control ADD COLUMN source_id integer; -- cartodb_id of the row
ALTER TABLE version_control ADD COLUMN table_name text; -- table name
ALTER TABLE version_control ADD COLUMN tg_op text; -- writing operation that was performed
ALTER TABLE version_control ADD COLUMN logged_at timestamp; -- timestamp for the operation
And create the function itself:
CREATE OR REPLACE FUNCTION carto_version_control() RETURNS TRIGGER AS $$
BEGIN
IF (TG_OP = 'DELETE') THEN
INSERT INTO version_control(the_geom, tg_op, data, source_id, table_name, logged_at)
SELECT OLD.the_geom, 'DELETE', row_to_json(OLD), OLD.cartodb_id, TG_TABLE_NAME::text, now();
RETURN OLD;
ELSIF (TG_OP = 'UPDATE') THEN
INSERT INTO version_control(the_geom, tg_op, data, source_id, table_name, logged_at)
SELECT NEW.the_geom, 'UPDATE', row_to_json(NEW), NEW.cartodb_id, TG_TABLE_NAME::text, now();
RETURN NEW;
ELSIF (TG_OP = 'INSERT') THEN
INSERT INTO version_control(the_geom, tg_op, data, source_id, table_name, logged_at)
SELECT NEW.the_geom, 'INSERT', row_to_json(NEW), NEW.cartodb_id, TG_TABLE_NAME::text, now();
RETURN NEW;
END IF;
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
Once we’ve created the function, we need to add a trigger to any table we want to audit:
CREATE TRIGGER carto_version_trigger
AFTER UPDATE OR DELETE OR INSERT ON dummy_dataset
FOR EACH ROW EXECUTE PROCEDURE version_control();
The trigger will be fired after each UPDATE, DELETE or INSERT operation that is made to dummy_dataset (remember to update the trigger with the proper table name). Then the carto_version_control() function will make an INSERT to version_control table, adding a row with data coming from the operation itself.
Each control_version row will log:
- The deleted/updated/inserted geometry
- The name of the operation that was made
- The whole modified row as a JSON object
cartodb_idfrom the modified row- The name of the table that was affected by the operation (useful in case we add the trigger to several different tables)
- Timestamp of when the trigger was fired
NOTE: After each INSERT that is made to a cartodbfied table, there is a subsequent UPDATE that is triggered to populate either the_geom or the_geom_webmercator as well as cartodb_id.
This is due to the triggers that are added to every table as part of the CDB_CartodbfyTable() proccess, and will result in almost identical rows in our version_control table that could help us understand what does it means to ‘cartodbfy a table’.
To retrieve information from version_control table, we could run a query like:
SELECT (json_populate_record(null::dummy_dataset, data)).*
FROM version_control LIMIT 1
Which will generate a row from the JSON data column in dummy_dataset table. We could also apply a filter to get more relevant information, for example:
SELECT
(json_populate_record(null::dummy_dataset, data)).*
FROM
version_control
WHERE
table_name LIKE 'dummy_dataset'
to get a row for each modified row in dummy_dataset table.
A different example
This is another example of stored procedure that will allow to insert a point with the public user role. This is most helpful in order to setup a point collector app.
CREATE OR REPLACE FUNCTION insertpoint(
lon numeric,
lat numeric,
tablename text
)
RETURNS TABLE(cartodb_id INT)
LANGUAGE 'plpgsql' SECURITY DEFINER
RETURNS NULL ON NULL INPUT AS $function$
DECLARE
sql text;
BEGIN
sql = 'WITH do_insert AS (
INSERT INTO ' ||quote_ident(tablename)|| '(the_geom)
VALUES '||'('||
'ST_SetSRID(ST_MakePoint('||lon||','||lat||'), 4326)'||')'||
'RETURNING cartodb_id)'||
'SELECT cartodb_id FROM do_insert';
RETURN QUERY EXECUTE sql;
END;
$function$
Further reading
-
This tutorial explains the basics of the PostgreSQL stored procedure language in a very friendly way. Many explanations from this document have been extracted from there.
-
The Pl/pgSQL section on the PostgreSQL documentation is a complete and detailed resource by itself, read it to learn all about pl/pgSQL.
-
Look and understand the different PostgreSQL/PostGIS functions that we have in the cartodb-postgresql repo or other postgreSQL related repos (dataservices…).
-
PostgreSQL, PostGIS blogs, etc; to find different PostGIS functions that you can build with pl/pgSQL. I would recommend looking for
Fun with PostGISand have some fun looking at what people do :)