This content applies to a previous version of CARTO

In October 2021 we released a new version of our platform. You can find the latest documentation at docs.carto.com

Back to Help Center

PostgreSQL Stored Procedures

What is a stored procedure?

PostgreSQL enables database functionality extension through user-defined functions via procedural languages, commonly called stored procedures. These allow you to create custom functions for reuse across applications and database workflows.

Basic structure of a PL/pgSQL function

CREATE FUNCTION function_name(argument1 type, argument2 type)
 RETURNS type AS
BEGIN
  staments;
END;
LANGUAGE 'language_name';

Key components:

  1. Function name with CREATE FUNCTION clause
  2. Parameter list with data types
  3. RETURNS statement specifying output type
  4. Code block between BEGIN and END;
  5. Procedural language designation (typically plpgsql)
  6. Optional conditions: STRICT, IMMUTABLE, or VOLATILE

Function conditions:

  • STRICT: Returns NULL for NULL inputs without evaluation
  • IMMUTABLE: Caches results for identical inputs; cannot modify database
  • VOLATILE: Results vary even with identical inputs

Calling functions

SELECT * FROM function_name(val1, val2);
SELECT function_name(val1, val2);
SELECT function_name(val1, val2) FROM table;

Input and output arguments

Define inputs with IN and outputs with OUT:

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;

Reference unnamed arguments via $1, $2, etc., or declare aliases in the DECLARE section.

Variables and constants

CREATE FUNCTION function_name(argument1 type, argument2 type)
 RETURNS type AS
 DECLARE
  variable_name data_type [:= value];
  constant_name CONSTANT data_type [:= value];
BEGIN
  staments;
END;
LANGUAGE language_name;

Errors and messages

Use RAISE statements with severity levels:

RAISE DEBUG 'debug message %', now();
RAISE LOG 'log message %', now();
RAISE INFO 'information message %', now();
RAISE NOTICE 'notice message %', now();
RAISE WARNING 'warning message %', now();
RAISE EXCEPTION 'error message %', now();

Only RAISE EXCEPTION appears in the CARTO Editor. Other levels display via SQL API responses in warnings, notices, and infos fields.

Dynamic PL/pgSQL functions

Use EXECUTE for dynamic SQL construction:

EXECUTE command-string [ INTO [STRICT] target ] [ USING expression [, ... ] ];

Features:

  • No plan caching; queries replan each execution
  • Variables must be inserted during command construction
  • INTO stores query results in variables
  • STRICT reports errors unless exactly one row returns

Functions returning no rows

Set RETURNS void for operations like INSERT, UPDATE, or DELETE:

CREATE OR REPLACE FUNCTION logfunc1(logtxt text) RETURNS void AS $$
    BEGIN
      INSERT INTO untitled_table_1 (description, currenttime) VALUES (logtxt, 'now');
    END;
$$ LANGUAGE plpgsql

Functions returning tables

Use RETURNS TABLE:

CREATE OR REPLACE FUNCTION get_freq_table()
RETURNS TABLE(
    name VARCHAR,
    frequency INT,
    total_frequency INT
)
AS $$
BEGIN
  RETURN QUERY
    SELECT column1, count(*), count(*) OVER()
      FROM table;
END;
$$ LANGUAGE 'plpgsql'

Functions with Security Definer

Enable anonymous users to perform writes via stored procedures:

CREATE OR REPLACE FUNCTION insertpoint(
    lon numeric,
    lat numeric,
    name 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)
        VALUES (ST_SetSRID(ST_MakePoint('||lon||','||lat||'), 4326),'
              || quote_literal(name)||')'
              ||'RETURNING cartodb_id)'
              ||'SELECT cartodb_id FROM do_insert';
  RETURN QUERY EXECUTE sql;
END;
$$;

GRANT EXECUTE ON FUNCTION insertpoint(numeric, numeric, text, text) TO publicuser;

Trigger functions

Triggers execute functions on table operations:

CREATE OR REPLACE FUNCTION tr_function()
RETURNS TRIGGER AS $$
BEGIN
  NEW.c3 = NEW.c1 + NEW.c2;
  RETURN NEW;
END;
$$ LANGUAGE 'plpgsql';

CREATE TRIGGER tr
BEFORE INSERT ON tableName
FOR EACH ROW EXECUTE PROCEDURE tr_function();

DROP TRIGGER triggerName ON tableName;

Control structures

IF statement

IF condition THEN
  statements;
ELSIF condition-2 THEN
  elsif-statements;
ELSE
  else-statements;
END IF;

CASE statement

Simple form:

CASE search-expression
  WHEN expression_1 THEN statements
  ELSE else-statements
END CASE;

Searched form:

CASE
  WHEN boolean-expression-1 THEN statements
  ELSE statements
END CASE;

LOOP statement

LOOP
   Statements;
   EXIT WHEN condition;
END LOOP;

WHILE loops

WHILE counter <= n LOOP
  counter := counter + 1;
END LOOP;

FOR loops

Integer range:

FOR loop_counter IN [ REVERSE ] from.. to [ BY expression ] LOOP
  statements
END LOOP;

Query results:

FOR target IN query LOOP
  statements
END LOOP;

Dynamic queries:

FOR row IN EXECUTE string_expression [ USING query_param ] LOOP
  statements
END LOOP;

PL/pgSQL and PostGIS

Create geometric functions combining procedural logic with PostGIS operations:

CREATE OR REPLACE FUNCTION regularPolygons(
    IN center geometry,
    IN radius numeric,
    IN number_of_sides integer
) RETURNS GEOMETRY AS $$
DECLARE
  geometry_wkt text := '';
  i integer := 0;
  angle numeric;
  ref_line geometry;
BEGIN
  ref_line = ST_MakeLine(center, ST_Transform(...));
  angle = radians(360::numeric/number_of_sides::numeric);

  WHILE (i < number_of_sides) LOOP
    geometry_wkt = geometry_wkt || (...);
    i = i + 1;
  END LOOP;

  RETURN geometry_wkt::geometry;
END;
$$ LANGUAGE plpgsql;

Exercise: Version control with triggers

Create audit logging:

CREATE TABLE version_control();
SELECT CDB_CartoDBFyTable('username', 'version_control');

ALTER TABLE version_control ADD COLUMN data json;
ALTER TABLE version_control ADD COLUMN source_id integer;
ALTER TABLE version_control ADD COLUMN table_name text;
ALTER TABLE version_control ADD COLUMN tg_op text;
ALTER TABLE version_control ADD COLUMN logged_at timestamp;

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;

CREATE TRIGGER carto_version_trigger
AFTER UPDATE OR DELETE OR INSERT ON dummy_dataset
    FOR EACH ROW EXECUTE PROCEDURE carto_version_control();

Retrieve audit data:

SELECT (json_populate_record(null::dummy_dataset, data)).*
FROM version_control
WHERE table_name LIKE 'dummy_dataset';