twigfiddle.com - Fiddle zttn94 v1 - Created on July 18, 2019 03:40

File

{% extends "parent.twig" %}
{% set table = "team" %}
{% set columns = ["team_id", "active_status", "file_code", "franchise_code", "bis_team_code", "created_by", "created_ts", "modified_by", "modified_ts"] %}
{% set primary_keys = ["team_id"] %}
{% set on_conflict_update_columns = ["active_status", "file_code", "franchise_code", "bis_team_code", "modified_by", "modified_at"] %}

File

{#- Template Variables -#}
{%- set table = table|default("") -%} {#- the name of the table  -#}
{%- set columns = columns|default([]) -%} {#- names of the columns in the table -#} 
{%- set primary_keys = primary_keys|default([]) -%} {#- names of columns defining the primary key -#}
{%- set on_conflict_update_columns = on_conflict_update_columns|default([]) -%} {#- columns to update in the event of a conflict -#}
{#- Internal Variables -#}
{#- NOTE: _context contains a mapping of column => value e.g. _context['team_id'] = 102 -#}
{%- set given_columns = keys(_context) -%} {#- the columns provided by the client -#}
{%- set use_columns = [] -%} {#- the final set of columns to generate this template -#}
{%- for column in columns -%}
    {%- if (_context[column]) -%}
        {%  set  use_columns = use_columns | merge(column) %}
    {%- endif -%}
{%- endfor -%}
{#- Generate the Query -#}
INSERT INTO {{ table }}(
{%- for column in use_columns -%}
    {{column}}{%- if loop.last == false -%},{%- endif -%}
{%- endfor -%}
)
VALUES(
{%- for column in use_columns -%}
    :{{column}}{%- if loop.last == false -%},{% endif -%}
{%- endfor -%}
)
{%- if(empty(on_conflict_update_columns) == false) %}
ON CONFLICT(
{%- if(empty(primary_keys) == false) -%}
    {{ join(primary_keys, ",") }}
{%- endif -%}
) DO UPDATE SET
{% for column in on_conflict_update_columns -%}
    {{column}} = EXCLUDED.{{column}}{% if (loop.last == false) -%},
{% endif %}
{%- endfor -%}
{%- endif -%}

Format

{"_context": {"team_id": 102}}