Database governance built for humans and agents — controlling changes and access across every major database.
Go cicddata-governancedata-maskingdata-securitydatabase-accessdatabase-governancedevopsdevsecopsflywaygitopsliquibasemongodbmysqloraclepostgresqlschema-migrationssecuritysql-clientsqlservertidb
- statement: |-
SELECT
MIN(bb) AS aa
FROM
(
SELECT
1 bb
) AS r
WHERE
1 = 1
;
defaultDatabase: db
metadata: |-
{
"name": "db",
"schemas": [
{
"name": "public"
}
]
}
querySpan:
type: 1
results:
- name: aa
sourcecolumns: []
isplainfield: false
sourcefieldpaths: []
selectasterisk: false
sourcecolumns: []
predicatecolumns: []
- description: unnest as table source with alias
statement: SELECT * FROM unnest(ARRAY[1,2,3], ARRAY[0,0,0]) as t(a, b);
defaultDatabase: db
metadata: |-
{
"name": "db",
"schemas": [
{
"name": "public"
}
]
}
querySpan:
type: 1
results:
- name: a
sourcecolumns: []
isplainfield: false
sourcefieldpaths: []
selectasterisk: false
- name: b
sourcecolumns: []
isplainfield: false
sourcefieldpaths: []
selectasterisk: false
sourcecolumns: []
predicatecolumns: []
- description: unnest as table source with alias
statement: SELECT * FROM unnest(ARRAY[1,2,3], ARRAY[0,0,0]) as t;
defaultDatabase: db
metadata: |-
{
"name": "db",
"schemas": [
{
"name": "public"
}
]
}
querySpan:
type: 1
results:
- name: unnest
sourcecolumns: []
isplainfield: false
sourcefieldpaths: []
selectasterisk: false
- name: unnest
sourcecolumns: []
isplainfield: false
sourcefieldpaths: []
selectasterisk: false
sourcecolumns: []
predicatecolumns: []
- description: unnest as table source with alias
statement: SELECT * FROM unnest(ARRAY[1,2,3]) as t;
defaultDatabase: db
metadata: |-
{
"name": "db",
"schemas": [
{
"name": "public"
}
]
}
querySpan:
type: 1
results:
- name: t
sourcecolumns: []
isplainfield: false
sourcefieldpaths: []
selectasterisk: false
sourcecolumns: []
predicatecolumns: []
- description: Function with if-else
statement: SELECT * from IF_QUERY();
defaultDatabase: db
metadata: |-
{
"name": "db",
"schemas": [
{
"name": "public",
"tables": [
{
"name": "t1",
"columns": [
{
"name": "a",
"type": "text"
},
{
"name": "b",
"type": "text"
},
{
"name": "c",
"type": "text"
}
]
},
{
"name": "t2",
"columns": [
{
"name": "c",
"type": "text"
},
{
"name": "d",
"type": "text"
},
{
"name": "e",
"type": "text"
}
]
}
],
"functions": [
{
"name": "if_query",
"definition": "CREATE OR REPLACE FUNCTION public.IF_QUERY() RETURNS TABLE(x int, xx int, xxx int) LANGUAGE plpgsql AS $function$ declare data_count int; BEGIN data_count := (select count(*) from public.t where a = 1); if (count(data_count) > 0) then RETURN QUERY SELECT a, b, c FROM t1; else RETURN QUERY SELECT c, d, e FROM t2; end if; END; $function$;"
}
]
}
]
}
querySpan:
type: 1
results:
- name: x
sourcecolumns:
- server: ""
database: db
schema: public
table: t1
column: a
- server: ""
database: db
schema: public
table: t2
column: c
isplainfield: false
sourcefieldpaths: []
selectasterisk: false
- name: xx
sourcecolumns:
- server: ""
database: db
schema: public
table: t1
column: b
- server: ""
database: db
schema: public
table: t2
column: d
isplainfield: false
sourcefieldpaths: []
selectasterisk: false
- name: xxx
sourcecolumns:
- server: ""
database: db
schema: public
table: t1
column: c
- server: ""
database: db
schema: public
table: t2
column: e
isplainfield: false
sourcefieldpaths: []
selectasterisk: false
sourcecolumns:
- server: ""
database: db
schema: public
table: t1
column: ""
- server: ""
database: db
schema: public
table: t2
column: ""
predicatecolumns: []
- description: Function with variables
statement: SELECT plpgsql_t(1);
defaultDatabase: db
metadata: |-
{
"name": "db",
"schemas": [
{
"name": "public",
"tables": [
{
"name": "t",
"columns": [
{
"name": "a",
"type": "text"
},
{
"name": "b",
"type": "text"
}
]
},
{
"name": "t2",
"columns": [
{
"name": "c",
"type": "text"
},
{
"name": "d",
"type": "text"
}
]
}
],
"functions": [
{
"name": "plpgsql_t",
"definition": "CREATE OR REPLACE FUNCTION plpgsql_t (x int) RETURNS TABLE(a int, b int) LANGUAGE plpgsql AS $$ DECLARE v_1 int := 0; v_2 int := 0; BEGIN v_1 := (select sum(a) from t where b = x group by b); v_2 := (select sum(c) from t2 where d = x group by d); RETURN QUERY select v_1, v_2; END; $$"
}
]
}
]
}
querySpan:
type: 1
results:
- name: plpgsql_t
sourcecolumns:
- server: ""
database: db
schema: public
table: t
column: a
- server: ""
database: db
schema: public
table: t2
column: c
isplainfield: false
sourcefieldpaths: []
selectasterisk: false
sourcecolumns:
- server: ""
database: db
schema: public
table: t
column: ""
- server: ""
database: db
schema: public
table: t2
column: ""
predicatecolumns:
- server: ""
database: db
schema: public
table: t
column: b
- server: ""
database: db
schema: public
table: t2
column: d
- description: Function Overloading
statement: SELECT plpgsql_t(1, 2);
defaultDatabase: db
metadata: |-
{
"name": "db",
"schemas": [
{
"name": "public",
"tables": [
{
"name": "t",
"columns": [
{
"name": "a",
"type": "text"
},
{
"name": "b",
"type": "text"
}
]
},
{
"name": "t2",
"columns": [
{
"name": "c",
"type": "text"
},
{
"name": "d",
"type": "text"
}
]
}
],
"functions": [
{
"name": "plpgsql_t",
"definition": "CREATE OR REPLACE FUNCTION plpgsql_t (x int) RETURNS TABLE(a int, b int) AS $$ BEGIN RETURN QUERY SELECT a, b FROM t WHERE a = $1; END; $$ LANGUAGE plpgsql;"
},
{
"name": "plpgsql_t",
"definition": "CREATE OR REPLACE FUNCTION plpgsql_t (x int, y int) RETURNS TABLE(a int, b int) AS $$ BEGIN RETURN QUERY SELECT c, d FROM t2 WHERE a = $1 AND b = $2; END; $$ LANGUAGE plpgsql;"
}
]
}
]
}
querySpan:
type: 1
results:
- name: plpgsql_t
sourcecolumns:
- server: ""
database: db
schema: public
table: t2
column: c
- server: ""
database: db
schema: public
table: t2
column: d
isplainfield: false
sourcefieldpaths: []
selectasterisk: false
sourcecolumns:
- server: ""
database: db
schema: public
table: t2
column: ""
predicatecolumns: []
- description: Bytebase Regression Test
statement: |-
WITH
issue_approvers AS (
SELECT
issue.id AS issue_id,
COALESCE(
array_agg(DISTINCT principal.email) FILTER (
WHERE
x.status = 'APPROVED'
),
'{}'
) AS approver_emails
FROM
issue
LEFT JOIN LATERAL jsonb_to_recordset(issue.payload -> 'approval' -> 'approvers') as x (status text, "principalId" int) ON TRUE
LEFT JOIN principal ON principal.id = x."principalId"
GROUP BY
issue.id
ORDER BY
issue.id
),
issue_releasers AS (
SELECT
issue.id AS issue_id,
COALESCE(
array_agg(DISTINCT principal.email) FILTER (
WHERE
task_run.creator_id IS NOT NULL
),
'{}'
) AS releaser_emails
FROM
issue
LEFT JOIN task ON issue.pipeline_id = task.pipeline_id
LEFT JOIN task_run ON task_run.task_id = task.id
LEFT JOIN principal ON task_run.creator_id = principal.id
GROUP BY
issue.id
ORDER BY
issue.id
)
SELECT
date_trunc('month', to_timestamp(issue.created_ts)) AS month,
COUNT(issue.id) AS issue_count,
ia.approver_emails,
ir.releaser_emails
FROM
issue
LEFT JOIN issue_approvers ia ON ia.issue_id = issue.id
LEFT JOIN issue_releasers ir ON ir.issue_id = issue.id
WHERE
issue.status = 'DONE'
AND ia.approver_emails @> ir.releaser_emails
AND ir.releaser_emails @> ia.approver_emails
AND array_length(ir.releaser_emails, 1) > 0
GROUP BY
month,
ia.approver_emails,
ir.releaser_emails
ORDER BY
month;
defaultDatabase: db
metadata: |-
{
"name": "db",
"schemas": [
{
"name": "public",
"tables": [
{
"name": "issue",
"columns": [
{
"name": "id",
"position": 1,
"defaultExpression": "nextval('issue_id_seq'::regclass)",
"type": "integer"
},
{
"name": "row_status",
"position": 2,
"defaultExpression": "'NORMAL'::row_status",
"type": "public.row_status"
},
{
"name": "creator_id",
"position": 3,
"type": "integer"
},
{
"name": "created_ts",
"position": 4,
"defaultExpression": "EXTRACT(epoch FROM now())",
"type": "bigint"
},
{
"name": "updater_id",
"position": 5,
"type": "integer"
},
{
"name": "updated_ts",
"position": 6,
"defaultExpression": "EXTRACT(epoch FROM now())",
"type": "bigint"
},
{
"name": "project_id",
"position": 7,
"type": "integer"
},
{
"name": "plan_id",
"position": 8,
"nullable": true,
"type": "bigint"
},
{
"name": "pipeline_id",
"position": 9,
"nullable": true,
"type": "integer"
},
{
"name": "name",
"position": 10,
"type": "text"
},
{
"name": "status",
"position": 11,
"type": "text"
},
{
"name": "type",
"position": 12,
"type": "text"
},
{
"name": "description",
"position": 13,
"defaultExpression": "''::text",
"type": "text"
},
{
"name": "assignee_id",
"position": 14,
"nullable": true,
"type": "integer"
},
{
"name": "assignee_need_attention",
"position": 15,
"defaultExpression": "false",
"type": "boolean"
},
{
"name": "payload",
"position": 16,
"defaultExpression": "'{}'::jsonb",
"type": "jsonb"
},
{
"name": "ts_vector",
"position": 17,
"nullable": true,
"type": "tsvector"
}
]
},
{
"name": "principal",
"columns": [
{
"name": "id",
"position": 1,
"defaultExpression": "nextval('principal_id_seq'::regclass)",
"type": "integer"
},
{
"name": "row_status",
"position": 2,
"defaultExpression": "'NORMAL'::row_status",
"type": "public.row_status"
},
{
"name": "creator_id",
"position": 3,
"type": "integer"
},
{
"name": "created_ts",
"position": 4,
"defaultExpression": "EXTRACT(epoch FROM now())",
"type": "bigint"
},
{
"name": "updater_id",
"position": 5,
"type": "integer"
},
{
"name": "updated_ts",
"position": 6,
"defaultExpression": "EXTRACT(epoch FROM now())",
"type": "bigint"
},
{
"name": "type",
"position": 7,
"type": "text"
},
{
"name": "name",
"position": 8,
"type": "text"
},
{
"name": "email",
"position": 9,
"type": "text"
},
{
"name": "password_hash",
"position": 10,
"type": "text"
},
{
"name": "phone",
"position": 11,
"defaultExpression": "''::text",
"type": "text"
},
{
"name": "mfa_config",
"position": 12,
"defaultExpression": "'{}'::jsonb",
"type": "jsonb"
}
]
},
{
"name": "task",
"columns": [
{
"name": "id",
"position": 1,
"defaultExpression": "nextval('task_id_seq'::regclass)",
"type": "integer"
},
{
"name": "row_status",
"position": 2,
"defaultExpression": "'NORMAL'::row_status",
"type": "public.row_status"
},
{
"name": "creator_id",
"position": 3,
"type": "integer"
},
{
"name": "created_ts",
"position": 4,
"defaultExpression": "EXTRACT(epoch FROM now())",
"type": "bigint"
},
{
"name": "updater_id",
"position": 5,
"type": "integer"
},
{
"name": "updated_ts",
"position": 6,
"defaultExpression": "EXTRACT(epoch FROM now())",
"type": "bigint"
},
{
"name": "pipeline_id",
"position": 7,
"type": "integer"
},
{
"name": "stage_id",
"position": 8,
"type": "integer"
},
{
"name": "instance_id",
"position": 9,
"type": "integer"
},
{
"name": "database_id",
"position": 10,
"nullable": true,
"type": "integer"
},
{
"name": "name",
"position": 11,
"type": "text"
},
{
"name": "status",
"position": 12,
"type": "text"
},
{
"name": "type",
"position": 13,
"type": "text"
},
{
"name": "payload",
"position": 14,
"defaultExpression": "'{}'::jsonb",
"type": "jsonb"
},
{
"name": "earliest_allowed_ts",
"position": 15,
"defaultExpression": "0",
"type": "bigint"
}
]
},
{
"name": "task_run",
"columns": [
{
"name": "id",
"position": 1,
"defaultExpression": "nextval('task_run_id_seq'::regclass)",
"type": "integer"
},
{
"name": "creator_id",
"position": 2,
"type": "integer"
},
{
"name": "created_ts",
"position": 3,
"defaultExpression": "EXTRACT(epoch FROM now())",
"type": "bigint"
},
{
"name": "updater_id",
"position": 4,
"type": "integer"
},
{
"name": "updated_ts",
"position": 5,
"defaultExpression": "EXTRACT(epoch FROM now())",
"type": "bigint"
},
{
"name": "task_id",
"position": 6,
"type": "integer"
},
{
"name": "attempt",
"position": 7,
"type": "integer"
},
{
"name": "name",
"position": 8,
"type": "text"
},
{
"name": "status",
"position": 9,
"type": "text"
},
{
"name": "started_ts",
"position": 10,
"defaultExpression": "0",
"type": "bigint"
},
{
"name": "code",
"position": 11,
"defaultExpression": "0",
"type": "integer"
},
{
"name": "result",
"position": 12,
"defaultExpression": "'{}'::jsonb",
"type": "jsonb"
},
{
"name": "sheet_id",
"position": 13,
"nullable": true,
"type": "integer"
}
]
}
]
}
]
}
querySpan:
type: 1
results:
- name: month
sourcecolumns:
- server: ""
database: db
schema: public
table: issue
column: created_ts
isplainfield: false
sourcefieldpaths: []
selectasterisk: false
- name: issue_count
sourcecolumns:
- server: ""
database: db
schema: public
table: issue
column: id
isplainfield: false
sourcefieldpaths: []
selectasterisk: false
- name: approver_emails
sourcecolumns:
- server: ""
database: db
schema: public
table: principal
column: email
isplainfield: false
sourcefieldpaths: []
selectasterisk: false
- name: releaser_emails
sourcecolumns:
- server: ""
database: db
schema: public
table: principal
column: email
isplainfield: false
sourcefieldpaths: []
selectasterisk: false
sourcecolumns:
- server: ""
database: db
schema: public
table: issue
column: ""
- server: ""
database: db
schema: public
table: principal
column: ""
- server: ""
database: db
schema: public
table: task
column: ""
- server: ""
database: db
schema: public
table: task_run
column: ""
predicatecolumns:
- server: ""
database: db
schema: public
table: issue
column: id
- server: ""
database: db
schema: public
table: issue
column: payload
- server: ""
database: db
schema: public
table: issue
column: pipeline_id
- server: ""
database: db
schema: public
table: issue
column: status
- server: ""
database: db
schema: public
table: principal
column: email
- server: ""
database: db
schema: public
table: principal
column: id
- server: ""
database: db
schema: public
table: task
column: id
- server: ""
database: db
schema: public
table: task
column: pipeline_id
- server: ""
database: db
schema: public
table: task
column: status
- server: ""
database: db
schema: public
table: task_run
column: creator_id
- server: ""
database: db
schema: public
table: task_run
column: status
- server: ""
database: db
schema: public
table: task_run
column: task_id
- description: Test For PLpgSQL User Defined Function in expression
statement: SELECT plpgsql_t(1);
defaultDatabase: db
metadata: |-
{
"name": "db",
"schemas": [
{
"name": "public",
"tables": [
{
"name": "t",
"columns": [
{
"name": "a",
"type": "text"
},
{
"name": "b",
"type": "text"
}
]
}
],
"functions": [
{
"name": "plpgsql_t",
"definition": "CREATE OR REPLACE FUNCTION plpgsql_t (x int) RETURNS TABLE(a int, b int) AS $$ BEGIN RETURN QUERY SELECT a, b FROM t WHERE a = $1; END; $$ LANGUAGE plpgsql;"
}
]
}
]
}
querySpan:
type: 1
results:
- name: plpgsql_t
sourcecolumns:
- server: ""
database: db
schema: public
table: t
column: a
- server: ""
database: db
schema: public
table: t
column: b
isplainfield: false
sourcefieldpaths: []
selectasterisk: false
sourcecolumns:
- server: ""
database: db
schema: public
table: t
column: ""
predicatecolumns:
- server: ""
database: db
schema: public
table: t
column: a
- description: json_object as target field
statement: 'SELECT json_object(''id'': a) FROM t'
defaultDatabase: db
metadata: |-
{
"name": "db",
"schemas": [
{
"name": "public",
"tables": [
{
"name": "t",
"columns": [
{
"name": "a",
"type": "text"
}
]
}
]
}
]
}
querySpan:
type: 1
results:
- name: json_object
sourcecolumns:
- server: ""
database: db
schema: public
table: t
column: a
isplainfield: false
sourcefieldpaths: []
selectasterisk: false
sourcecolumns:
- server: ""
database: db
schema: public
table: t
column: ""
predicatecolumns: []
- description: json_array as target field
statement: SELECT json_array(a) FROM t;
defaultDatabase: db
metadata: |-
{
"name": "db",
"schemas": [
{
"name": "public",
"tables": [
{
"name": "t",
"columns": [
{
"name": "a",
"type": "text"
}
]
}
]
}
]
}
querySpan:
type: 1
results:
- name: json_array
sourcecolumns:
- server: ""
database: db
schema: public
table: t
column: a
isplainfield: false
sourcefieldpaths: []
selectasterisk: false
sourcecolumns:
- server: ""
database: db
schema: public
table: t
column: ""
predicatecolumns: []
- description: json_each as target field
statement: SELECT jsonb_each(a) FROM t;
defaultDatabase: db
metadata: |-
{
"name": "db",
"schemas": [
{
"name": "public",
"tables": [
{
"name": "t",
"columns": [
{
"name": "a",
"type": "text"
}
]
}
]
}
]
}
querySpan:
type: 1
results:
- name: jsonb_each
sourcecolumns:
- server: ""
database: db
schema: public
table: t
column: a
isplainfield: false
sourcefieldpaths: []
selectasterisk: false
sourcecolumns:
- server: ""
database: db
schema: public
table: t
column: ""
predicatecolumns: []
- description: json_each acted as table source
statement: SELECT * FROM t, jsonb_each(a) WHERE id = 1;
defaultDatabase: db
metadata: |-
{
"name": "db",
"schemas": [
{
"name": "public",
"tables": [
{
"name": "t",
"columns": [
{
"name": "a",
"type": "text"
}
]
}
]
}
]
}
querySpan:
type: 1
results:
- name: a
sourcecolumns:
- server: ""
database: db
schema: public
table: t
column: a
isplainfield: true
sourcefieldpaths: []
selectasterisk: false
- name: jsonb_each
sourcecolumns:
- server: ""
database: db
schema: public
table: t
column: a
isplainfield: false
sourcefieldpaths: []
selectasterisk: false
sourcecolumns:
- server: ""
database: db
schema: public
table: t
column: ""
predicatecolumns:
- server: ""
database: db
schema: public
table: t
column: a
- description: to_jsonb accept table as argument with from table alias
statement: SELECT to_jsonb(t1) FROM t t1;
defaultDatabase: db
metadata: |-
{
"name": "db",
"schemas": [
{
"name": "public",
"tables": [
{
"name": "t",
"columns": [
{
"name": "a",
"type": "text"
}
]
}
]
}
]
}
querySpan:
type: 1
results:
- name: to_jsonb
sourcecolumns:
- server: ""
database: db
schema: public
table: t
column: a
isplainfield: false
sourcefieldpaths: []
selectasterisk: false
sourcecolumns:
- server: ""
database: db
schema: public
table: t
column: ""
predicatecolumns: []
- description: Test for JSON function
statement: SELECT row_to_json(t) FROM t;
defaultDatabase: db
metadata: |-
{
"name": "db",
"schemas": [
{
"name": "public",
"tables": [
{
"name": "t",
"columns": [
{
"name": "a",
"type": "text"
}
]
}
]
}
]
}
querySpan:
type: 1
results:
- name: row_to_json
sourcecolumns:
- server: ""
database: db
schema: public
table: t
column: a
isplainfield: false
sourcefieldpaths: []
selectasterisk: false
sourcecolumns:
- server: ""
database: db
schema: public
table: t
column: ""
predicatecolumns: []
- description: Test For unnest function
statement: SELECT * FROM unnest(ARRAY[1,2,3], ARRAY[4,5,6]);
defaultDatabase: db
metadata: |-
{
"name": "db",
"schemas": [
{
"name": "public",
"tables": [
{
"name": "t",
"columns": [
{
"name": "a",
"type": "text"
}
]
}
]
}
]
}
querySpan:
type: 1
results:
- name: unnest
sourcecolumns: []
isplainfield: false
sourcefieldpaths: []
selectasterisk: false
- name: unnest
sourcecolumns: []
isplainfield: false
sourcefieldpaths: []
selectasterisk: false
sourcecolumns: []
predicatecolumns: []
- description: Test For unnest function
statement: SELECT * FROM unnest(ARRAY[1,2,3]);
defaultDatabase: db
metadata: |-
{
"name": "db",
"schemas": [
{
"name": "public",
"tables": [
{
"name": "t",
"columns": [
{
"name": "a",
"type": "text"
}
]
}
]
}
]
}
querySpan:
type: 1
results:
- name: unnest
sourcecolumns: []
isplainfield: false
sourcefieldpaths: []
selectasterisk: false
sourcecolumns: []
predicatecolumns: []
- description: Test For System Function As Table Source
statement: SELECT * FROM generate_series(1, 3);
defaultDatabase: db
metadata: |-
{
"name": "db",
"schemas": [
{
"name": "public",
"tables": [
{
"name": "t",
"columns": [
{
"name": "a",
"type": "text"
}
]
}
]
}
]
}
querySpan:
type: 1
results:
- name: generate_series
sourcecolumns: []
isplainfield: false
sourcefieldpaths: []
selectasterisk: false
sourcecolumns: []
predicatecolumns: []
- description: Test For System Function As Table Source
statement: SELECT * FROM generate_subscripts(ARRAY[1,2,3], 1);
defaultDatabase: db
metadata: |-
{
"name": "db",
"schemas": [
{
"name": "public",
"tables": [
{
"name": "t",
"columns": [
{
"name": "a",
"type": "text"
}
]
}
]
}
]
}
querySpan:
type: 1
results:
- name: generate_subscripts
sourcecolumns: []
isplainfield: false
sourcefieldpaths: []
selectasterisk: false
sourcecolumns: []
predicatecolumns: []
- description: Test For alias.* with join
statement: SELECT t1.*, t2.* FROM t t1 JOIN (select * from t) t2 ON t1.a = t2.a;
defaultDatabase: db
metadata: |-
{
"name": "db",
"schemas": [
{
"name": "public",
"tables": [
{
"name": "t",
"columns": [
{
"name": "a",
"type": "text"
}
]
}
]
}
]
}
querySpan:
type: 1
results:
- name: a
sourcecolumns:
- server: ""
database: db
schema: public
table: t
column: a
isplainfield: true
sourcefieldpaths: []
selectasterisk: false
- name: a
sourcecolumns:
- server: ""
database: db
schema: public
table: t
column: a
isplainfield: true
sourcefieldpaths: []
selectasterisk: false
sourcecolumns:
- server: ""
database: db
schema: public
table: t
column: ""
predicatecolumns:
- server: ""
database: db
schema: public
table: t
column: a
- description: Test For Complex PLpgSQL Function As Table Source
statement: SELECT * FROM plpgsql_complex_t(1);
defaultDatabase: db
metadata: |-
{
"name": "db",
"schemas": [
{
"name": "public",
"tables": [
{
"name": "t",
"columns": [
{
"name": "a",
"type": "text"
}
]
},
{
"name": "t2",
"columns": [
{
"name": "a",
"type": "text"
}
]
}
],
"functions": [
{
"name": "plpgsql_complex_t",
"definition": "CREATE OR REPLACE FUNCTION plpgsql_t (x int) RETURNS TABLE(a int) AS $$ BEGIN IF x = 1 THEN RETURN QUERY SELECT a FROM t WHERE a = $1; ELSE RETURN QUERY SELECT a FROM t2 WHERE a = $1; END IF; END; $$ LANGUAGE plpgsql;"
}
]
}
]
}
querySpan:
type: 1
results:
- name: a
sourcecolumns:
- server: ""
database: db
schema: public
table: t
column: a
- server: ""
database: db
schema: public
table: t2
column: a
isplainfield: false
sourcefieldpaths: []
selectasterisk: false
sourcecolumns:
- server: ""
database: db
schema: public
table: t
column: ""
- server: ""
database: db
schema: public
table: t2
column: ""
predicatecolumns:
- server: ""
database: db
schema: public
table: t
column: a
- server: ""
database: db
schema: public
table: t2
column: a
- description: Test For PLpgSQL Function As Table Source
statement: SELECT * FROM plpgsql_t(1);
defaultDatabase: db
metadata: |-
{
"name": "db",
"schemas": [
{
"name": "public",
"tables": [
{
"name": "t",
"columns": [
{
"name": "a",
"type": "text"
}
]
}
],
"functions": [
{
"name": "plpgsql_t",
"definition": "CREATE OR REPLACE FUNCTION plpgsql_t (x int) RETURNS TABLE(a int) AS $$ BEGIN RETURN QUERY SELECT a FROM t WHERE a = $1; END; $$ LANGUAGE plpgsql;"
}
]
}
]
}
querySpan:
type: 1
results:
- name: a
sourcecolumns:
- server: ""
database: db
schema: public
table: t
column: a
isplainfield: false
sourcefieldpaths: []
selectasterisk: false
sourcecolumns:
- server: ""
database: db
schema: public
table: t
column: ""
predicatecolumns:
- server: ""
database: db
schema: public
table: t
column: a
- description: Test For Cascade Function As Table Source
statement: SELECT * FROM cascade_t(1);
defaultDatabase: db
metadata: |-
{
"name": "db",
"schemas": [
{
"name": "public",
"tables": [
{
"name": "t",
"columns": [
{
"name": "a",
"type": "text"
}
]
}
],
"functions": [
{
"name": "select_t",
"definition": "CREATE FUNCTION select_t (x int) RETURNS TABLE(a int) AS $$ SELECT a FROM t WHERE a = $1; $$ LANGUAGE SQL;"
},
{
"name": "cascade_t",
"definition": "CREATE FUNCTION cascade_t (x int) RETURNS TABLE(a int) AS $$ SELECT * FROM select_t($1); $$ LANGUAGE SQL;"
}
]
}
]
}
querySpan:
type: 1
results:
- name: a
sourcecolumns:
- server: ""
database: db
schema: public
table: t
column: a
isplainfield: false
sourcefieldpaths: []
selectasterisk: false
sourcecolumns:
- server: ""
database: db
schema: public
table: t
column: ""
predicatecolumns:
- server: ""
database: db
schema: public
table: t
column: a
- description: Test For Function As Table Source
statement: SELECT * FROM select_t(1);
defaultDatabase: db
metadata: |-
{
"name": "db",
"schemas": [
{
"name": "public",
"tables": [
{
"name": "t",
"columns": [
{
"name": "a",
"type": "text"
}
]
}
],
"functions": [
{
"name": "select_t",
"definition": "CREATE FUNCTION select_t (x int) RETURNS TABLE(a int) AS $$ SELECT a FROM t WHERE a = $1; $$ LANGUAGE SQL;"
}
]
}
]
}
querySpan:
type: 1
results:
- name: a
sourcecolumns:
- server: ""
database: db
schema: public
table: t
column: a
isplainfield: false
sourcefieldpaths: []
selectasterisk: false
sourcecolumns:
- server: ""
database: db
schema: public
table: t
column: ""
predicatecolumns:
- server: ""
database: db
schema: public
table: t
column: a
- description: Test For Explain statements
statement: EXPLAIN SELECT 1;
defaultDatabase: db
metadata: |-
{
"name": "db",
"schemas": [
{
"name": "public",
"tables": [
{
"name": "t",
"columns": [
{
"name": "a",
"type": "text"
}
]
}
]
}
]
}
querySpan:
type: 2
results: []
sourcecolumns: []
predicatecolumns: []
- description: Test for simple select const
statement: SELECT 1;
defaultDatabase: db
metadata: |-
{
"name": "db",
"schemas": [
{
"name": "public",
"tables": [
{
"name": "t",
"columns": [
{
"name": "a",
"type": "text"
}
]
}
]
}
]
}
querySpan:
type: 1
results:
- name: ?column?
sourcecolumns: []
isplainfield: false
sourcefieldpaths: []
selectasterisk: false
sourcecolumns: []
predicatecolumns: []
- description: Test asterisk and column
statement: SELECT a, * FROM t;
defaultDatabase: db
metadata: |-
{
"name": "db",
"schemas": [
{
"name": "public",
"tables": [
{
"name": "t",
"columns": [
{
"name": "a",
"type": "text"
},
{
"name": "b",
"type": "text"
},
{
"name": "c",
"type": "text"
},
{
"name": "d",
"type": "text"
}
]
}
]
}
]
}
querySpan:
type: 1
results:
- name: a
sourcecolumns:
- server: ""
database: db
schema: public
table: t
column: a
isplainfield: false
sourcefieldpaths: []
selectasterisk: false
- name: a
sourcecolumns:
- server: ""
database: db
schema: public
table: t
column: a
isplainfield: true
sourcefieldpaths: []
selectasterisk: false
- name: b
sourcecolumns:
- server: ""
database: db
schema: public
table: t
column: b
isplainfield: true
sourcefieldpaths: []
selectasterisk: false
- name: c
sourcecolumns:
- server: ""
database: db
schema: public
table: t
column: c
isplainfield: true
sourcefieldpaths: []
selectasterisk: false
- name: d
sourcecolumns:
- server: ""
database: db
schema: public
table: t
column: d
isplainfield: true
sourcefieldpaths: []
selectasterisk: false
sourcecolumns:
- server: ""
database: db
schema: public
table: t
column: ""
predicatecolumns: []
- description: Happy path for a simple select statement
statement: SELECT * FROM t
defaultDatabase: db
metadata: |-
{
"name": "db",
"schemas": [
{
"name": "public",
"tables": [
{
"name": "t",
"columns": [
{
"name": "a",
"type": "text"
},
{
"name": "b",
"type": "text"
},
{
"name": "c",
"type": "text"
},
{
"name": "d",
"type": "text"
}
]
}
]
}
]
}
querySpan:
type: 1
results:
- name: a
sourcecolumns:
- server: ""
database: db
schema: public
table: t
column: a
isplainfield: true
sourcefieldpaths: []
selectasterisk: false
- name: b
sourcecolumns:
- server: ""
database: db
schema: public
table: t
column: b
isplainfield: true
sourcefieldpaths: []
selectasterisk: false
- name: c
sourcecolumns:
- server: ""
database: db
schema: public
table: t
column: c
isplainfield: true
sourcefieldpaths: []
selectasterisk: false
- name: d
sourcecolumns:
- server: ""
database: db
schema: public
table: t
column: d
isplainfield: true
sourcefieldpaths: []
selectasterisk: false
sourcecolumns:
- server: ""
database: db
schema: public
table: t
column: ""
predicatecolumns: []
- description: Referenced table fields by different format
statement: SELECT a, t.b, public.t.c, db.public.t.d FROM t;
defaultDatabase: db
metadata: |-
{
"name": "db",
"schemas": [
{
"name": "public",
"tables": [
{
"name": "t",
"columns": [
{
"name": "a",
"type": "text"
},
{
"name": "b",
"type": "text"
},
{
"name": "c",
"type": "text"
},
{
"name": "d",
"type": "text"
}
]
}
]
}
]
}
querySpan:
type: 1
results:
- name: a
sourcecolumns:
- server: ""
database: db
schema: public
table: t
column: a
isplainfield: false
sourcefieldpaths: []
selectasterisk: false
- name: b
sourcecolumns:
- server: ""
database: db
schema: public
table: t
column: b
isplainfield: false
sourcefieldpaths: []
selectasterisk: false
- name: c
sourcecolumns:
- server: ""
database: db
schema: public
table: t
column: c
isplainfield: false
sourcefieldpaths: []
selectasterisk: false
- name: d
sourcecolumns:
- server: ""
database: db
schema: public
table: t
column: d
isplainfield: false
sourcefieldpaths: []
selectasterisk: false
sourcecolumns:
- server: ""
database: db
schema: public
table: t
column: ""
predicatecolumns: []
- description: Scalar subquery in select fields
statement: SELECT 1 AS col_1, (SELECT(2)) AS col_2, (SELECT AVG(a + b * c) FROM t) AS avg_a_b_c FROM t;
defaultDatabase: db
metadata: |-
{
"name": "db",
"schemas": [
{
"name": "public",
"tables": [
{
"name": "t",
"columns": [
{
"name": "a",
"type": "text"
},
{
"name": "b",
"type": "text"
},
{
"name": "c",
"type": "text"
},
{
"name": "d",
"type": "text"
}
]
}
]
}
]
}
querySpan:
type: 1
results:
- name: col_1
sourcecolumns: []
isplainfield: false
sourcefieldpaths: []
selectasterisk: false
- name: col_2
sourcecolumns: []
isplainfield: false
sourcefieldpaths: []
selectasterisk: false
- name: avg_a_b_c
sourcecolumns:
- server: ""
database: db
schema: public
table: t
column: a
- server: ""
database: db
schema: public
table: t
column: b
- server: ""
database: db
schema: public
table: t
column: c
isplainfield: false
sourcefieldpaths: []
selectasterisk: false
sourcecolumns:
- server: ""
database: db
schema: public
table: t
column: ""
predicatecolumns: []
- description: Multiple-Row subquery in where clause
statement: SELECT 1 FROM t WHERE a IN (SELECT a FROM t);
defaultDatabase: db
metadata: |-
{
"name": "db",
"schemas": [
{
"name": "public",
"tables": [
{
"name": "t",
"columns": [
{
"name": "a",
"type": "text"
},
{
"name": "b",
"type": "text"
},
{
"name": "c",
"type": "text"
},
{
"name": "d",
"type": "text"
}
]
}
]
}
]
}
querySpan:
type: 1
results:
- name: ?column?
sourcecolumns: []
isplainfield: false
sourcefieldpaths: []
selectasterisk: false
sourcecolumns:
- server: ""
database: db
schema: public
table: t
column: ""
predicatecolumns:
- server: ""
database: db
schema: public
table: t
column: a
- description: Correlated subquery in the target field clause.
statement: SELECT city, (SELECT COUNT(*) FROM paintings p WHERE g.id = p.gallery_id) AS total_paintings FROM galleries g;
defaultDatabase: db
metadata: |-
{
"name": "db",
"schemas": [
{
"name": "public",
"tables": [
{
"name": "galleries",
"columns": [
{
"name": "id",
"type": "text"
},
{
"name": "city",
"type": "text"
}
]
},
{
"name": "paintings",
"columns": [
{
"name": "gallery_id",
"type": "text"
},
{
"name": "artist_id",
"type": "text"
},
{
"name": "title",
"type": "text"
},
{
"name": "price",
"type": "text"
}
]
}
]
}
]
}
querySpan:
type: 1
results:
- name: city
sourcecolumns:
- server: ""
database: db
schema: public
table: galleries
column: city
isplainfield: false
sourcefieldpaths: []
selectasterisk: false
- name: total_paintings
sourcecolumns: []
isplainfield: false
sourcefieldpaths: []
selectasterisk: false
sourcecolumns:
- server: ""
database: db
schema: public
table: galleries
column: ""
- server: ""
database: db
schema: public
table: paintings
column: ""
predicatecolumns:
- server: ""
database: db
schema: public
table: galleries
column: id
- server: ""
database: db
schema: public
table: paintings
column: gallery_id
- description: Test for functions
statement: SELECT max(a), a-b AS c1, a=b AS c2, a>b, b in (a, c, d) from (SELECT * FROM t) result;
defaultDatabase: db
metadata: |-
{
"name": "db",
"schemas": [
{
"name": "public",
"tables": [
{
"name": "t",
"columns": [
{
"name": "a",
"type": "text"
},
{
"name": "b",
"type": "text"
},
{
"name": "c",
"type": "text"
},
{
"name": "d",
"type": "text"
}
]
}
]
}
]
}
querySpan:
type: 1
results:
- name: max
sourcecolumns:
- server: ""
database: db
schema: public
table: t
column: a
isplainfield: false
sourcefieldpaths: []
selectasterisk: false
- name: c1
sourcecolumns:
- server: ""
database: db
schema: public
table: t
column: a
- server: ""
database: db
schema: public
table: t
column: b
isplainfield: false
sourcefieldpaths: []
selectasterisk: false
- name: c2
sourcecolumns:
- server: ""
database: db
schema: public
table: t
column: a
- server: ""
database: db
schema: public
table: t
column: b
isplainfield: false
sourcefieldpaths: []
selectasterisk: false
- name: ?column?
sourcecolumns:
- server: ""
database: db
schema: public
table: t
column: a
- server: ""
database: db
schema: public
table: t
column: b
isplainfield: false
sourcefieldpaths: []
selectasterisk: false
- name: ?column?
sourcecolumns:
- server: ""
database: db
schema: public
table: t
column: a
- server: ""
database: db
schema: public
table: t
column: b
- server: ""
database: db
schema: public
table: t
column: c
- server: ""
database: db
schema: public
table: t
column: d
isplainfield: false
sourcefieldpaths: []
selectasterisk: false
sourcecolumns:
- server: ""
database: db
schema: public
table: t
column: ""
predicatecolumns: []
- description: Test for SELECT partial FROM JOIN clause
statement: SELECT t1.*, t2.c, 0 FROM t1 JOIN t2 ON 1 = 1;
defaultDatabase: db
metadata: |-
{
"name": "db",
"schemas": [
{
"name": "public",
"tables": [
{
"name": "t1",
"columns": [
{
"name": "a",
"type": "text"
},
{
"name": "b",
"type": "text"
}
]
},
{
"name": "t2",
"columns": [
{
"name": "c",
"type": "text"
},
{
"name": "d",
"type": "text"
}
]
}
]
}
]
}
querySpan:
type: 1
results:
- name: a
sourcecolumns:
- server: ""
database: db
schema: public
table: t1
column: a
isplainfield: true
sourcefieldpaths: []
selectasterisk: false
- name: b
sourcecolumns:
- server: ""
database: db
schema: public
table: t1
column: b
isplainfield: true
sourcefieldpaths: []
selectasterisk: false
- name: c
sourcecolumns:
- server: ""
database: db
schema: public
table: t2
column: c
isplainfield: false
sourcefieldpaths: []
selectasterisk: false
- name: ?column?
sourcecolumns: []
isplainfield: false
sourcefieldpaths: []
selectasterisk: false
sourcecolumns:
- server: ""
database: db
schema: public
table: t1
column: ""
- server: ""
database: db
schema: public
table: t2
column: ""
predicatecolumns: []
- description: Test for JOIN with USING clause
statement: SELECT * FROM t AS t1 JOIN t AS t2 USING(a);
defaultDatabase: db
metadata: |-
{
"name": "db",
"schemas": [
{
"name": "public",
"tables": [
{
"name": "t",
"columns": [
{
"name": "a",
"type": "text"
},
{
"name": "b",
"type": "text"
},
{
"name": "c",
"type": "text"
},
{
"name": "d",
"type": "text"
}
]
}
]
}
]
}
querySpan:
type: 1
results:
- name: a
sourcecolumns:
- server: ""
database: db
schema: public
table: t
column: a
isplainfield: true
sourcefieldpaths: []
selectasterisk: false
- name: b
sourcecolumns:
- server: ""
database: db
schema: public
table: t
column: b
isplainfield: true
sourcefieldpaths: []
selectasterisk: false
- name: c
sourcecolumns:
- server: ""
database: db
schema: public
table: t
column: c
isplainfield: true
sourcefieldpaths: []
selectasterisk: false
- name: d
sourcecolumns:
- server: ""
database: db
schema: public
table: t
column: d
isplainfield: true
sourcefieldpaths: []
selectasterisk: false
- name: b
sourcecolumns:
- server: ""
database: db
schema: public
table: t
column: b
isplainfield: true
sourcefieldpaths: []
selectasterisk: false
- name: c
sourcecolumns:
- server: ""
database: db
schema: public
table: t
column: c
isplainfield: true
sourcefieldpaths: []
selectasterisk: false
- name: d
sourcecolumns:
- server: ""
database: db
schema: public
table: t
column: d
isplainfield: true
sourcefieldpaths: []
selectasterisk: false
sourcecolumns:
- server: ""
database: db
schema: public
table: t
column: ""
predicatecolumns:
- server: ""
database: db
schema: public
table: t
column: a
- description: Simple CTE
statement: WITH t1 AS (SELECT * FROM t) SELECT * FROM t1;
defaultDatabase: db
metadata: |-
{
"name": "db",
"schemas": [
{
"name": "public",
"tables": [
{
"name": "t",
"columns": [
{
"name": "a",
"type": "text"
},
{
"name": "b",
"type": "text"
},
{
"name": "c",
"type": "text"
},
{
"name": "d",
"type": "text"
}
]
}
]
}
]
}
querySpan:
type: 1
results:
- name: a
sourcecolumns:
- server: ""
database: db
schema: public
table: t
column: a
isplainfield: true
sourcefieldpaths: []
selectasterisk: false
- name: b
sourcecolumns:
- server: ""
database: db
schema: public
table: t
column: b
isplainfield: true
sourcefieldpaths: []
selectasterisk: false
- name: c
sourcecolumns:
- server: ""
database: db
schema: public
table: t
column: c
isplainfield: true
sourcefieldpaths: []
selectasterisk: false
- name: d
sourcecolumns:
- server: ""
database: db
schema: public
table: t
column: d
isplainfield: true
sourcefieldpaths: []
selectasterisk: false
sourcecolumns:
- server: ""
database: db
schema: public
table: t
column: ""
predicatecolumns: []
- description: Multi-level CTE
statement: WITH tt2 AS (WITH tt2 AS (SELECT * FROM t) SELECT MAX(a) FROM tt2) SELECT * FROM tt2;
defaultDatabase: db
metadata: |-
{
"name": "db",
"schemas": [
{
"name": "public",
"tables": [
{
"name": "t",
"columns": [
{
"name": "a",
"type": "text"
},
{
"name": "b",
"type": "text"
},
{
"name": "c",
"type": "text"
},
{
"name": "d",
"type": "text"
}
]
}
]
}
]
}
querySpan:
type: 1
results:
- name: max
sourcecolumns:
- server: ""
database: db
schema: public
table: t
column: a
isplainfield: false
sourcefieldpaths: []
selectasterisk: false
sourcecolumns:
- server: ""
database: db
schema: public
table: t
column: ""
predicatecolumns: []
- description: Test for CTE rename fields name
statement: WITH t1(d, c, b, a) AS (SELECT * FROM t) SELECT * FROM t1;
defaultDatabase: db
metadata: |-
{
"name": "db",
"schemas": [
{
"name": "public",
"tables": [
{
"name": "t",
"columns": [
{
"name": "a",
"type": "text"
},
{
"name": "b",
"type": "text"
},
{
"name": "c",
"type": "text"
},
{
"name": "d",
"type": "text"
}
]
}
]
}
]
}
querySpan:
type: 1
results:
- name: d
sourcecolumns:
- server: ""
database: db
schema: public
table: t
column: a
isplainfield: true
sourcefieldpaths: []
selectasterisk: false
- name: c
sourcecolumns:
- server: ""
database: db
schema: public
table: t
column: b
isplainfield: true
sourcefieldpaths: []
selectasterisk: false
- name: b
sourcecolumns:
- server: ""
database: db
schema: public
table: t
column: c
isplainfield: true
sourcefieldpaths: []
selectasterisk: false
- name: a
sourcecolumns:
- server: ""
database: db
schema: public
table: t
column: d
isplainfield: true
sourcefieldpaths: []
selectasterisk: false
sourcecolumns:
- server: ""
database: db
schema: public
table: t
column: ""
predicatecolumns: []
- description: Test for Recursive Common Table Expression dependent closures
statement: WITH RECURSIVE t1(cc1, cc2, cc3, n) AS (SELECT a AS c1, b AS c2, c AS c3, 1 AS n FROM t UNION SELECT cc1*cc2, cc2 + cc1, cc3 * cc2, n+1 FROM t1 WHERE n < 10) SELECT * FROM t1;
defaultDatabase: db
metadata: |-
{
"name": "db",
"schemas": [
{
"name": "public",
"tables": [
{
"name": "t",
"columns": [
{
"name": "a",
"type": "text"
},
{
"name": "b",
"type": "text"
},
{
"name": "c",
"type": "text"
},
{
"name": "d",
"type": "text"
}
]
}
]
}
]
}
querySpan:
type: 1
results:
- name: cc1
sourcecolumns:
- server: ""
database: db
schema: public
table: t
column: a
- server: ""
database: db
schema: public
table: t
column: b
isplainfield: false
sourcefieldpaths: []
selectasterisk: false
- name: cc2
sourcecolumns:
- server: ""
database: db
schema: public
table: t
column: a
- server: ""
database: db
schema: public
table: t
column: b
isplainfield: false
sourcefieldpaths: []
selectasterisk: false
- name: cc3
sourcecolumns:
- server: ""
database: db
schema: public
table: t
column: a
- server: ""
database: db
schema: public
table: t
column: b
- server: ""
database: db
schema: public
table: t
column: c
isplainfield: false
sourcefieldpaths: []
selectasterisk: false
- name: "n"
sourcecolumns: []
isplainfield: false
sourcefieldpaths: []
selectasterisk: false
sourcecolumns:
- server: ""
database: db
schema: public
table: t
column: ""
predicatecolumns: []
- description: Test for Non-Recursive Common Table Expression with RECURSIVE key words
statement: WITH RECURSIVE t1 AS (SELECT 1 AS c1, 2 AS c2, 3 AS c3, 4 AS c4 UNION SELECT a, b, d, c FROM t) SELECT * FROM t1;
defaultDatabase: db
metadata: |-
{
"name": "db",
"schemas": [
{
"name": "public",
"tables": [
{
"name": "t",
"columns": [
{
"name": "a",
"type": "text"
},
{
"name": "b",
"type": "text"
},
{
"name": "c",
"type": "text"
},
{
"name": "d",
"type": "text"
}
]
}
]
}
]
}
querySpan:
type: 1
results:
- name: c1
sourcecolumns:
- server: ""
database: db
schema: public
table: t
column: a
isplainfield: false
sourcefieldpaths: []
selectasterisk: false
- name: c2
sourcecolumns:
- server: ""
database: db
schema: public
table: t
column: b
isplainfield: false
sourcefieldpaths: []
selectasterisk: false
- name: c3
sourcecolumns:
- server: ""
database: db
schema: public
table: t
column: d
isplainfield: false
sourcefieldpaths: []
selectasterisk: false
- name: c4
sourcecolumns:
- server: ""
database: db
schema: public
table: t
column: c
isplainfield: false
sourcefieldpaths: []
selectasterisk: false
sourcecolumns:
- server: ""
database: db
schema: public
table: t
column: ""
predicatecolumns: []
- description: query system tables
statement: select * from information_schema.tables;
defaultDatabase: db
metadata: |-
{
"name": "db",
"schemas": [
{
"name": "public",
"tables": [
{
"name": "t",
"columns": [
{
"name": "a",
"type": "text"
}
]
}
]
}
]
}
querySpan:
type: 3
results: []
sourcecolumns:
- server: ""
database: db
schema: information_schema
table: tables
column: ""
predicatecolumns: []
- description: Test table.* with JOIN on different tables for masking
statement: SELECT users.*, payments.* FROM users JOIN payments ON users.id = payments.user_id;
defaultDatabase: db
metadata: |-
{
"name": "db",
"schemas": [
{
"name": "public",
"tables": [
{
"name": "users",
"columns": [
{"name": "id", "type": "text"},
{"name": "name", "type": "text"},
{"name": "email", "type": "text"}
]
},
{
"name": "payments",
"columns": [
{"name": "id", "type": "text"},
{"name": "user_id", "type": "text"},
{"name": "amount", "type": "text"}
]
}
]
}
]
}
querySpan:
type: 1
results:
- name: id
sourcecolumns:
- server: ""
database: db
schema: public
table: users
column: id
isplainfield: true
sourcefieldpaths: []
selectasterisk: false
- name: name
sourcecolumns:
- server: ""
database: db
schema: public
table: users
column: name
isplainfield: true
sourcefieldpaths: []
selectasterisk: false
- name: email
sourcecolumns:
- server: ""
database: db
schema: public
table: users
column: email
isplainfield: true
sourcefieldpaths: []
selectasterisk: false
- name: id
sourcecolumns:
- server: ""
database: db
schema: public
table: payments
column: id
isplainfield: true
sourcefieldpaths: []
selectasterisk: false
- name: user_id
sourcecolumns:
- server: ""
database: db
schema: public
table: payments
column: user_id
isplainfield: true
sourcefieldpaths: []
selectasterisk: false
- name: amount
sourcecolumns:
- server: ""
database: db
schema: public
table: payments
column: amount
isplainfield: true
sourcefieldpaths: []
selectasterisk: false
sourcecolumns:
- server: ""
database: db
schema: public
table: payments
column: ""
- server: ""
database: db
schema: public
table: users
column: ""
predicatecolumns:
- server: ""
database: db
schema: public
table: payments
column: user_id
- server: ""
database: db
schema: public
table: users
column: id
- description: jsonb_array_elements as table source
statement: SELECT * FROM t, jsonb_array_elements(a) AS elems WHERE id = 1;
defaultDatabase: db
metadata: |-
{
"name": "db",
"schemas": [
{
"name": "public",
"tables": [
{
"name": "t",
"columns": [
{
"name": "a",
"type": "text"
}
]
}
]
}
]
}
querySpan:
type: 1
results:
- name: a
sourcecolumns:
- server: ""
database: db
schema: public
table: t
column: a
isplainfield: true
sourcefieldpaths: []
selectasterisk: false
- name: jsonb_array_elements
sourcecolumns:
- server: ""
database: db
schema: public
table: t
column: a
isplainfield: false
sourcefieldpaths: []
selectasterisk: false
sourcecolumns:
- server: ""
database: db
schema: public
table: t
column: ""
predicatecolumns:
- server: ""
database: db
schema: public
table: t
column: a
- description: jsonb_array_elements with column alias
statement: SELECT * FROM t, jsonb_array_elements(a) AS elems(val) WHERE id = 1;
defaultDatabase: db
metadata: |-
{
"name": "db",
"schemas": [
{
"name": "public",
"tables": [
{
"name": "t",
"columns": [
{
"name": "a",
"type": "text"
}
]
}
]
}
]
}
querySpan:
type: 1
results:
- name: a
sourcecolumns:
- server: ""
database: db
schema: public
table: t
column: a
isplainfield: true
sourcefieldpaths: []
selectasterisk: false
- name: val
sourcecolumns:
- server: ""
database: db
schema: public
table: t
column: a
isplainfield: false
sourcefieldpaths: []
selectasterisk: false
sourcecolumns:
- server: ""
database: db
schema: public
table: t
column: ""
predicatecolumns:
- server: ""
database: db
schema: public
table: t
column: a
- description: jsonb_array_elements_text as table source with alias
statement: SELECT * FROM t CROSS JOIN LATERAL jsonb_array_elements_text(a) AS part WHERE id = 1;
defaultDatabase: db
metadata: |-
{
"name": "db",
"schemas": [
{
"name": "public",
"tables": [
{
"name": "t",
"columns": [
{
"name": "a",
"type": "text"
}
]
}
]
}
]
}
querySpan:
type: 1
results:
- name: a
sourcecolumns:
- server: ""
database: db
schema: public
table: t
column: a
isplainfield: true
sourcefieldpaths: []
selectasterisk: false
- name: jsonb_array_elements_text
sourcecolumns:
- server: ""
database: db
schema: public
table: t
column: a
isplainfield: false
sourcefieldpaths: []
selectasterisk: false
sourcecolumns:
- server: ""
database: db
schema: public
table: t
column: ""
predicatecolumns:
- server: ""
database: db
schema: public
table: t
column: a
- description: array subscript in select list
statement: |-
SELECT
c.id,
c.scores[array_length(c.scores, 1)] AS last_score
FROM t1 c
WHERE c.finished_at IS NOT NULL
ORDER BY c.finished_at DESC
LIMIT 10;
defaultDatabase: db
metadata: |-
{
"name": "db",
"schemas": [
{
"name": "public",
"tables": [
{
"name": "t1",
"columns": [
{"name": "id", "type": "text"},
{"name": "scores", "type": "text"},
{"name": "finished_at", "type": "text"}
]
}
]
}
]
}
querySpan:
type: 1
results:
- name: id
sourcecolumns:
- server: ""
database: db
schema: public
table: t1
column: id
isplainfield: false
sourcefieldpaths: []
selectasterisk: false
- name: last_score
sourcecolumns:
- server: ""
database: db
schema: public
table: t1
column: scores
isplainfield: false
sourcefieldpaths: []
selectasterisk: false
sourcecolumns:
- server: ""
database: db
schema: public
table: t1
column: ""
predicatecolumns:
- server: ""
database: db
schema: public
table: t1
column: finished_at