Revision 10296
Added by Aaron Marcuse-Kubitza over 11 years ago
| schemas/util.sql | ||
|---|---|---|
| 1023 | 1023 |
|
| 1024 | 1024 |
|
| 1025 | 1025 |
-- |
| 1026 |
-- Name: mk_derived_col(col_ref, text); Type: FUNCTION; Schema: util; Owner: - |
|
| 1026 |
-- Name: mk_derived_col(col_ref, text, boolean); Type: FUNCTION; Schema: util; Owner: -
|
|
| 1027 | 1027 |
-- |
| 1028 | 1028 |
|
| 1029 |
CREATE FUNCTION mk_derived_col(col col_ref, expr text) RETURNS void |
|
| 1029 |
CREATE FUNCTION mk_derived_col(col col_ref, expr text, overwrite boolean DEFAULT false) RETURNS void
|
|
| 1030 | 1030 |
LANGUAGE plpgsql STRICT |
| 1031 | 1031 |
AS $_$ |
| 1032 | 1032 |
DECLARE |
| 1033 | 1033 |
type regtype = util.typeof(expr, col.table_::text::regtype); |
| 1034 | 1034 |
col_name_sql text = quote_ident(col.name); |
| 1035 | 1035 |
BEGIN |
| 1036 |
PERFORM util.create_if_not_exists($$ |
|
| 1037 |
ALTER TABLE $$||col.table_||$$ ADD COLUMN $$||col_name_sql||$$ $$||type||$$; |
|
| 1036 |
PERFORM util.create_if_not_exists((CASE WHEN overwrite THEN '' ELSE $$
|
|
| 1037 |
ALTER TABLE $$||col.table_||$$ ADD COLUMN $$||col_name_sql||$$ $$||type||$$;$$ END)||$$
|
|
| 1038 | 1038 |
ALTER TABLE $$||col.table_||$$ ALTER COLUMN $$||col_name_sql||$$ TYPE $$||type||$$ USING |
| 1039 | 1039 |
$$||expr||$$; |
| 1040 | 1040 |
$$); |
| ... | ... | |
| 1043 | 1043 |
|
| 1044 | 1044 |
|
| 1045 | 1045 |
-- |
| 1046 |
-- Name: FUNCTION mk_derived_col(col col_ref, expr text); Type: COMMENT; Schema: util; Owner: - |
|
| 1046 |
-- Name: FUNCTION mk_derived_col(col col_ref, expr text, overwrite boolean); Type: COMMENT; Schema: util; Owner: -
|
|
| 1047 | 1047 |
-- |
| 1048 | 1048 |
|
| 1049 |
COMMENT ON FUNCTION mk_derived_col(col col_ref, expr text) IS 'idempotent'; |
|
| 1049 |
COMMENT ON FUNCTION mk_derived_col(col col_ref, expr text, overwrite boolean) IS 'idempotent';
|
|
| 1050 | 1050 |
|
| 1051 | 1051 |
|
| 1052 | 1052 |
-- |
Also available in: Unified diff
schemas/util.sql: mk_derived_col(): support using this function to overwrite an existing column (i.e. as a general-purpose function to perform in-place update with ALTER COLUMN TYPE USING)