meta/002-identifiers-nongen.sql at master · aquameta/meta · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
set search_path=meta;

-- for some reason, generator isn't generating these.  they're commented out.
create function meta.field_id_to_row_id(field_id meta.field_id) returns meta.row_id as $_$ select meta.row_id((field_id).schema_name, (field_id).relation_name, (field_id).pk_column_names, (field_id).pk_values) $_$ immutable language sql;
create cast (meta.field_id as meta.row_id) with function meta.field_id_to_row_id(meta.field_id) as assignment;


------------------------------------------------------------------------
-- helpers
-- non-standard constructors that do sensible things
------------------------------------------------------------------------

-- field_id constructor taking a row_id
create function meta.field_id(row_id meta.row_id, column_name text) returns meta.field_id as $$
    select meta.field_id(row_id.schema_name, row_id.relation_name, row_id.pk_column_names, row_id.pk_values, column_name);
$$ language sql;


-- field_id constructor non-array pk
create function meta.field_id( schema_name text, relation_name text, pk_column_name text, pk_value text, column_name text) returns meta.field_id as $$
    select meta.field_id(schema_name, relation_name, array[pk_column_name], array[pk_value], column_name);
$$ language sql;


-- single key row_id constructor
create function meta.row_id(schema_name text, relation_name text, pk_column_name text, pk_value text) returns meta.row_id as $_$ select meta.row_id(schema_name, relation_name, array[pk_column_name], array[pk_value]) $_$ immutable language sql;


------------------------------------------------------------------------
-- pk_stmt()
-- helper function for iterating primary key arrays and generating a stmt fragment.
------------------------------------------------------------------------

-- template is rendered by format(), using positional argument notation.
--     1: pk_column_names[i]
--     2: pk_values[i]
--     3: i
--
-- select _pk_stmt (
--     array['id','other_id'],
--     array[public.uuid_generate_v4()::text,public.uuid_generate_v4()::text],
--     '(row_id).pk_values[%3$s] = x.%1$I'
-- );
--                               _pk_stmt
-- ---------------------------------------------------------------------
--  (row_id).pk_values[1] = x.id and (row_id).pk_values[2] = x.other_id

create or replace function meta._pk_stmt(pk_column_names text[], pk_values text[], template text, delimeter text default ' and ') returns text as $$
    declare
        pk_comparisons text[];
        column_name text;
        i integer;
    begin
        i := 1;
        foreach column_name in array pk_column_names loop
            pk_comparisons[i] := format(template, pk_column_names[i], pk_values[i], i);
            i := i + 1;
        end loop;
        return array_to_string(pk_comparisons, delimeter);
    end
$$ language plpgsql;

/*
select meta._pk_stmt (
    meta.row_id(
        'public',
        'foo',
        array['id','other_id'],
        array[public.uuid_generate_v4()::text,public.uuid_generate_v4()::text]
    ),
    '(row_id).pk_values[%3$s] = x.%1$I',
    ' OR '
);
                              _pk_stmt
--------------------------------------------------------------------
 (row_id).pk_values[1] = x.id OR (row_id).pk_values[2] = x.other_id
*/

create function meta._pk_stmt(row_id meta.row_id, template text, delimeter text default ' and ') returns text as $$
    select meta._pk_stmt((row_id).pk_column_names, (row_id).pk_values, template, delimeter);
$$ language sql;


create or replace function meta.field_id_literal_value(field_id meta.field_id) returns text as $$
declare
    literal_value text;
    stmt text;
    pk_stmt text;
begin
    pk_stmt := meta._pk_stmt (
        (field_id).pk_column_names,
        (field_id).pk_values,
        '%1$I = %2$L'
    );

    stmt := format('select %I::text from %I.%I where %s',
        (field_id).column_name,
        (field_id).schema_name,
        (field_id).relation_name,
        pk_stmt
    );
    -- raise notice 'stmt: %', stmt;

    execute stmt into literal_value;

    return literal_value;
-- TODO: is this correct?  this fires when the table doesn't exist etc.
exception when others then
    raise warning 'field_id_literal_value exception on %: %', field_id, SQLERRM;
    return null;
end
$$ language plpgsql stable;


create or replace function meta.row_exists(in row_id meta.row_id, out answer boolean) as $$
    declare
        stmt text;
        pk_comparisons text[];
        pk_comparison_stmt text;
        column_name text;
        i integer;
    begin
        -- generate the pk comparisons line
        i := 1;
        foreach column_name in array row_id.pk_column_names loop
            pk_comparisons[i] := quote_ident((row_id).pk_column_names[i]) || '::text = ' || quote_literal((row_id).pk_values[i]);
            i := i + 1;
        end loop;
        pk_comparison_stmt := array_to_string(pk_comparisons, ' and ');


        stmt := format (
            -- 'select (count(*) = 1) from %I.%I where %I::text = %L',
            'select (count(*) = 1) from %I.%I where %s',
                (row_id).schema_name,
                (row_id).relation_name,
                pk_comparison_stmt
            );

        raise debug '%', stmt;
        execute stmt into answer;

    exception
        when undefined_table then
            answer := false;
    end;
$$ language plpgsql;