Postgres: Setting Values of Fields Using SQL Functions

Introduction

Let's say you want to set the value of some fields as the output of some custom SQL functions (a.k.a. stored procedures). This is useful to set values of fields which depend on other fields passed in the input. E.g. set submission_time of an online quiz as 1 hour from the start_time.

This can be achieved by:

1. Modifying the table to allow the columns we want to be set by the SQL functions to be nullable (to allow the initial insert before the SQL function is run).
2. Creating an insert/update trigger on the table that calls your SQL function and sets the output values in the output columns.
3. Making your mutation requests without setting the SQL function output columns.

Note

This approach enforces the value set in the field to always be the result of the defined SQL function even if a value is explicitly passed in the insert object.

For example, say we have a table sql_function_table with columns input and output and we would like to set the value of the output column as the uppercased value of the string received in the input field.

Step 1: Modify the table

Modify the table sql_function_table and make its output column nullable.

Console

Open the Console and head to Data -> [sql_function_table] -> Modify:

CLI

Create a migration manually and add the following SQL statement to the up.sql file:

ALTER TABLE "public"."sql_function_table" ALTER COLUMN "output" DROP NOT NULL;

Add the following statement to the down.sql file in case you need to roll back the above statement:

ALTER TABLE "public"."sql_function_table" ALTER COLUMN "output" SET NOT NULL;

Apply the migration by running:

hasura migrate apply

API

You can modify a table column by using the run_sql schema API:

POST /v2/query HTTP/1.1
Content-Type: application/json
X-Hasura-Role: admin

{
  "type": "run_sql",
  "args": {
    "source": "<db_name>",
    "sql": "ALTER TABLE sql_function_table ALTER COLUMN output DROP NOT NULL;"
  }
}

Step 2: Create a trigger

The below SQL defines a trigger which will simply uppercase the value passed in the input field and set it to the output field whenever an insert or update is made to the sql_function_table:

CREATE FUNCTION test_func() RETURNS trigger AS $emp_stamp$
      BEGIN
          NEW.output := UPPER(NEW.input);
          RETURN NEW;
      END;
  $emp_stamp$ LANGUAGE plpgsql;

  CREATE TRIGGER test_trigger BEFORE INSERT OR UPDATE ON sql_function_table
      FOR EACH ROW EXECUTE PROCEDURE test_func();

Console

Head to Data -> SQL and run the above SQL:

CLI

Create a migration manually and add the above SQL to the up.sql file. Also, add a statement to revert the previous statement to the down.sql.

Apply the migration by running:

hasura migrate apply

API

You can create a trigger by using the run_sql schema API:

POST /v2/query HTTP/1.1
Content-Type: application/json
X-Hasura-Role: admin

{
  "type": "run_sql",
  "args": {
    "source": "<db_name>",
    "sql": "<above SQL>"
  }
}

Step 3: Run an insert mutation

Run a mutation to insert an object with (input = "yabba dabba doo!", output=null) and you'll see the output value (output="YABBA DABBA DOO!") will be set automatically.

GraphiQL

Query Variables

Request Headers

Documentation Explorer

⚲

No Schema Available

Also see

• Postgres: Setting default values for fields using Postgres defaults
• Postgres: Setting values for fields using role-based column presets