Hi
2015-08-21 7:15 GMT+02:00 Pavel Stehule <pavel.steh...@gmail.com>:
>
>
> 2015-08-20 21:16 GMT+02:00 Jim Nasby <jim.na...@bluetreble.com>:
>
>> On 8/19/15 7:22 PM, Tom Lane wrote:
>>
>>> Jim Nasby <jim.na...@bluetreble.com> writes:
>>>
>>>> Don't say "parse names for things other than tables". Only a minority
>>>>> of the types of objects used in the database have names that meet this
>>>>> specification.
>>>>>
>>>>
>>> Really? My impression is that almost everything that's not a shared
>>>> object allows for a schema...
>>>>
>>>
>>> Tables meet this naming spec. Columns, functions, operators, operator
>>> classes/families, collations, constraints, and conversions do not (you
>>> need more data to name them). Schemas, databases, languages, extensions,
>>> and some other things also do not, because you need *less* data to name
>>> them. Types also don't really meet this naming spec, because you need to
>>> contend with special cases like "int[]" or "timestamp with time zone".
>>> So this proposal doesn't seem very carefully thought-through to me,
>>> or at least the use case is much narrower than it could be.
>>>
>>> Also, if "object does not exist" isn't supposed to be an error case,
>>> what of "name is not correctly formatted"? It seems a bit arbitrary
>>> to me to throw an error in one case but not the other.
>>>
>>
>> I think the important point here is this is *parse*_ident(). It's not
>> meant to guarantee an object actually exists, what kind of object it is, or
>> whether it's syntactically correct. It's meant only to separate an
>> identifier into it's 3 (or in some cases 2) components. If this was as
>> simple as string_to_array(foo, '.') then it'd be a bit pointless, but it's
>> obviously a lot more complex than that.
>
>
> parsing composite identifier is pretty complex - and almost all work is
> done - it just need SQL envelope only
>
here is the patch
It is really trivial - all heavy work was done done before.
Regards
Pavel
>
> Pavel
>
>
>>
>> --
>> Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
>> Data in Trouble? Get it in Treble! http://BlueTreble.com
>>
>
>
commit 1546ec8d173c4fa91e08012af7fbfe0d64585ef0
Author: Pavel Stehule <pavel.steh...@gmail.com>
Date: Sun Aug 23 17:43:20 2015 +0200
initial
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 617d0b3..5d678bc 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -1707,6 +1707,23 @@
<row>
<entry>
<indexterm>
+ <primary>parse_ident</primary>
+ </indexterm>
+ <literal><function>parse_ident(<parameter>qualified_identifier</parameter> <type>text</type>,
+ OUT <parameter>dbname</parameter> <type>text</type>, OUT <parameter>schemaname</parameter> <type>text</type>,
+ OUT <parameter>objectname</parameter> <type>text</type>)</function></literal>
+ </entry>
+ <entry><type>record</type></entry>
+ <entry>Split <parameter>qualified identifier</parameter> to parts <parameter>dbname</parameter>,
+ <parameter>schemaname</parameter> and <parameter>objectname</parameter>.
+ </entry>
+ <entry><literal>parse_ident('"SomeSchema".someTable')</literal></entry>
+ <entry><literal>(,SomeSchema,sometable)</literal></entry>
+ </row>
+
+ <row>
+ <entry>
+ <indexterm>
<primary>pg_client_encoding</primary>
</indexterm>
<literal><function>pg_client_encoding()</function></literal>
diff --git a/src/backend/utils/adt/misc.c b/src/backend/utils/adt/misc.c
index c0495d9..19f87db 100644
--- a/src/backend/utils/adt/misc.c
+++ b/src/backend/utils/adt/misc.c
@@ -21,7 +21,9 @@
#include <unistd.h>
#include "access/sysattr.h"
+#include "access/htup_details.h"
#include "catalog/catalog.h"
+#include "catalog/namespace.h"
#include "catalog/pg_tablespace.h"
#include "catalog/pg_type.h"
#include "commands/dbcommands.h"
@@ -598,3 +600,59 @@ pg_column_is_updatable(PG_FUNCTION_ARGS)
PG_RETURN_BOOL((events & REQ_EVENTS) == REQ_EVENTS);
}
+
+/*
+ * parse_ident - decompose text identifier to basic three parts
+ */
+Datum
+parse_ident(PG_FUNCTION_ARGS)
+{
+ text *qualname = PG_GETARG_TEXT_PP(0);
+ List *name_list;
+ RangeVar *rv;
+ Datum values[3];
+ bool nulls[3];
+ TupleDesc tupdesc;
+
+ name_list = stringToQualifiedNameList(text_to_cstring(qualname));
+ rv = makeRangeVarFromNameList(name_list);
+
+ /* Prepare result tuple desc */
+ tupdesc = CreateTemplateTupleDesc(3, false);
+ TupleDescInitEntry(tupdesc, (AttrNumber) 1, "dbname",
+ TEXTOID, -1, 0);
+ TupleDescInitEntry(tupdesc, (AttrNumber) 2, "schemaname",
+ TEXTOID, -1, 0);
+ TupleDescInitEntry(tupdesc, (AttrNumber) 3, "objectname",
+ TEXTOID, -1, 0);
+
+ BlessTupleDesc(tupdesc);
+
+ /* Fill returned values */
+ if (rv->catalogname != NULL)
+ {
+ values[0] = CStringGetTextDatum(rv->catalogname);
+ nulls[0] = false;
+ }
+ else
+ nulls[0] = true;
+
+ if (rv->schemaname != NULL)
+ {
+ values[1] = CStringGetTextDatum(rv->schemaname);
+ nulls[1] = false;
+ }
+ else
+ nulls[1] = true;
+
+ if (rv->relname != NULL)
+ {
+ values[2] = CStringGetTextDatum(rv->relname);
+ nulls[2] = false;
+ }
+ else
+ nulls[2] = true;
+
+ PG_RETURN_DATUM(HeapTupleGetDatum(
+ heap_form_tuple(tupdesc, values, nulls)));
+}
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index ddf7c67..68a694a 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -3516,6 +3516,9 @@ DESCR("I/O");
DATA(insert OID = 4086 ( to_regnamespace PGNSP PGUID 12 1 0 0 0 f f f f t f s 1 0 4089 "2275" _null_ _null_ _null_ _null_ _null_ to_regnamespace _null_ _null_ _null_ ));
DESCR("convert namespace name to regnamespace");
+DATA(insert OID = 3300 ( parse_ident PGNSP PGUID 12 1 0 0 0 f f f f t f i 1 0 2249 "25" "{25,25,25,25}" "{i,o,o,o}" "{identifier,dbname,schemaname,objectname}" _null_ _null_ parse_ident _null_ _null_ _null_ ));
+DESCR("parse pattern dbname.schema.object");
+
DATA(insert OID = 2246 ( fmgr_internal_validator PGNSP PGUID 12 1 0 0 0 f f f f t f s 1 0 2278 "26" _null_ _null_ _null_ _null_ _null_ fmgr_internal_validator _null_ _null_ _null_ ));
DESCR("(internal)");
DATA(insert OID = 2247 ( fmgr_c_validator PGNSP PGUID 12 1 0 0 0 f f f f t f s 1 0 2278 "26" _null_ _null_ _null_ _null_ _null_ fmgr_c_validator _null_ _null_ _null_ ));
diff --git a/src/include/utils/builtins.h b/src/include/utils/builtins.h
index fc1679e..0cb491d 100644
--- a/src/include/utils/builtins.h
+++ b/src/include/utils/builtins.h
@@ -495,6 +495,7 @@ extern Datum pg_typeof(PG_FUNCTION_ARGS);
extern Datum pg_collation_for(PG_FUNCTION_ARGS);
extern Datum pg_relation_is_updatable(PG_FUNCTION_ARGS);
extern Datum pg_column_is_updatable(PG_FUNCTION_ARGS);
+extern Datum parse_ident(PG_FUNCTION_ARGS);
/* oid.c */
extern Datum oidin(PG_FUNCTION_ARGS);
diff --git a/src/test/regress/expected/name.out b/src/test/regress/expected/name.out
index b359d52..662633f 100644
--- a/src/test/regress/expected/name.out
+++ b/src/test/regress/expected/name.out
@@ -124,3 +124,14 @@ SELECT '' AS two, c.f1 FROM NAME_TBL c WHERE c.f1 ~ '.*asdf.*';
(2 rows)
DROP TABLE NAME_TBL;
+DO $$
+DECLARE r record;
+BEGIN
+ r := parse_ident('Schemax.Tabley');
+ RAISE NOTICE '%', format('%I.%I', r.schemaname, r.objectname);
+ r := parse_ident('"SchemaX"."TableY"');
+ RAISE NOTICE '%', format('%I.%I', r.schemaname, r.objectname);
+END;
+$$;
+NOTICE: schemax.tabley
+NOTICE: "SchemaX"."TableY"
diff --git a/src/test/regress/sql/name.sql b/src/test/regress/sql/name.sql
index 1c7a671..964ecf2 100644
--- a/src/test/regress/sql/name.sql
+++ b/src/test/regress/sql/name.sql
@@ -52,3 +52,13 @@ SELECT '' AS three, c.f1 FROM NAME_TBL c WHERE c.f1 ~ '[0-9]';
SELECT '' AS two, c.f1 FROM NAME_TBL c WHERE c.f1 ~ '.*asdf.*';
DROP TABLE NAME_TBL;
+
+DO $$
+DECLARE r record;
+BEGIN
+ r := parse_ident('Schemax.Tabley');
+ RAISE NOTICE '%', format('%I.%I', r.schemaname, r.objectname);
+ r := parse_ident('"SchemaX"."TableY"');
+ RAISE NOTICE '%', format('%I.%I', r.schemaname, r.objectname);
+END;
+$$;
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
