Thread: Additional accessors via the Extension API ?
Suppose I have defined an additional type in a PG extension. Is it possible to add custom accessors to that type -much like jsonb does- but use an API/hook without touching the corePG grammar & parser? Hypothetical Examples: Assuming I have a TextFile type I’d like to implement syntax like: (‘/home/me/a.txt’::TextFile).firstline (‘/home/me/a.txt’::TextFile).lastline (‘/home/me/a.txt’::TextFile).countlines() (‘/home/me/a.txt’::TextFile).size() (‘/home/me/a.txt’::TextFile).datemodified() The only relevant patch I could find is [1] but it’s a dead-end [1] https://www.postgresql.org/message-id/20210501072458.adqjoaqnmhg4l34l%40nol
> Suppose I have defined an additional type in a PG extension. > > Is it possible to add custom accessors to that type -much like jsonb does- but use an API/hook without touching the corePG grammar & parser? > > Hypothetical Examples: > > Assuming I have a TextFile type I’d like to implement syntax like: > > (‘/home/me/a.txt’::TextFile).firstline > (‘/home/me/a.txt’::TextFile).lastline > (‘/home/me/a.txt’::TextFile).countlines() > (‘/home/me/a.txt’::TextFile).size() > (‘/home/me/a.txt’::TextFile).datemodified() Off on a tangent but would file_fdw help in any way ? Karsten
Hi, On Sun, Feb 20, 2022 at 08:07:20AM +0200, Markur Sens wrote: > Suppose I have defined an additional type in a PG extension. > > Is it possible to add custom accessors to that type -much like jsonb does- > but use an API/hook without touching the core PG grammar & parser? Unfortunately no. > Hypothetical Examples: > > Assuming I have a TextFile type I’d like to implement syntax like: > > (‘/home/me/a.txt’::TextFile).firstline > (‘/home/me/a.txt’::TextFile).lastline > (‘/home/me/a.txt’::TextFile).countlines() > (‘/home/me/a.txt’::TextFile).size() > (‘/home/me/a.txt’::TextFile).datemodified() Maybe you could rely on some old grammar hack to have something a bit similar, as (expr).funcname is an alias for funcname(expr). For instance: # create function f1(int) returns text as $$ begin return 'val: ' || $1::text; end; $$ language plpgsql; # create table t as select 1 as id; # select (5).f1, (id).f1 from t; f1 | f1 --------+-------- val: 5 | val: 1 (1 row) I don't know if that would be enough for you needs. Otherwise, the only option would be tocreate an operator instead, like mytype -> 'myaccessor' or something like that.
> On 20 Feb 2022, at 12:12 PM, Julien Rouhaud <rjuju123@gmail.com> wrote: > > Hi, > > On Sun, Feb 20, 2022 at 08:07:20AM +0200, Markur Sens wrote: >> Suppose I have defined an additional type in a PG extension. >> >> Is it possible to add custom accessors to that type -much like jsonb does- >> but use an API/hook without touching the core PG grammar & parser? > > Unfortunately no. > >> Hypothetical Examples: >> >> Assuming I have a TextFile type I’d like to implement syntax like: >> >> (‘/home/me/a.txt’::TextFile).firstline >> (‘/home/me/a.txt’::TextFile).lastline >> (‘/home/me/a.txt’::TextFile).countlines() >> (‘/home/me/a.txt’::TextFile).size() >> (‘/home/me/a.txt’::TextFile).datemodified() > > Maybe you could rely on some old grammar hack to have something a bit similar, > as (expr).funcname is an alias for funcname(expr). For instance: Is this documented & expected behavior or it’s just happens to work? > > # create function f1(int) returns text as $$ > begin > return 'val: ' || $1::text; > end; > $$ language plpgsql; > > # create table t as select 1 as id; > > # select (5).f1, (id).f1 from t; > f1 | f1 > --------+-------- > val: 5 | val: 1 > (1 row) > > I don't know if that would be enough for you needs. Otherwise, the only option > would be tocreate an operator instead, like mytype -> 'myaccessor' or something > like that. Yes, that’s what I’m doing at the moment: Syntax like type -> ‘accessor’ is pretty straight forward to implement as an operator as the rightarg is text. Things get more complicating as I’m adding support for mytype -> function(arg=1) for that case I have to create an intermediate type of function(arg) so that I can then define the left and right args forthe -> operator. But it’s a lot of boilerplate code.
On Sun, Feb 20, 2022 at 12:31:22PM +0200, Markur Sens wrote: > > > > Maybe you could rely on some old grammar hack to have something a bit similar, > > as (expr).funcname is an alias for funcname(expr). For instance: > > Is this documented & expected behavior or it’s just happens to work? I don't think it's documented but it's an expected behavior, see https://github.com/postgres/postgres/blob/master/src/backend/parser/parse_func.c#L57-L88 /* * Parse a function call * * For historical reasons, Postgres tries to treat the notations tab.col * and col(tab) as equivalent: if a single-argument function call has an * argument of complex type and the (unqualified) function name matches * any attribute of the type, we can interpret it as a column projection. * Conversely a function of a single complex-type argument can be written * like a column reference, allowing functions to act like computed columns. * * If both interpretations are possible, we prefer the one matching the * syntactic form, but otherwise the form does not matter. * * Hence, both cases come through here. If fn is null, we're dealing with * column syntax not function syntax. In the function-syntax case, * the FuncCall struct is needed to carry various decoration that applies * to aggregate and window functions. [...]
> On 20 Feb 2022, at 12:35 PM, Julien Rouhaud <rjuju123@gmail.com> wrote: > > On Sun, Feb 20, 2022 at 12:31:22PM +0200, Markur Sens wrote: >>> >>> Maybe you could rely on some old grammar hack to have something a bit similar, >>> as (expr).funcname is an alias for funcname(expr). For instance: >> >> Is this documented & expected behavior or it’s just happens to work? > > I don't think it's documented but it's an expected behavior, see > > https://github.com/postgres/postgres/blob/master/src/backend/parser/parse_func.c#L57-L88 > Ah thanks for this > /* > * Parse a function call > * > * For historical reasons, Postgres tries to treat the notations tab.col > * and col(tab) as equivalent: if a single-argument function call has an > * argument of complex type and the (unqualified) function name matches > * any attribute of the type, we can interpret it as a column projection. and the (unqualified) function name matches * any attribute of the type, we can interpret it as a column projection. > * Conversely a function of a single complex-type argument can be written > * like a column reference, allowing functions to act like computed columns. > * > * If both interpretations are possible, we prefer the one matching the > * syntactic form, but otherwise the form does not matter. > * > * Hence, both cases come through here. If fn is null, we're dealing with > * column syntax not function syntax. In the function-syntax case, > * the FuncCall struct is needed to carry various decoration that applies > * to aggregate and window functions. > [...]
Julien Rouhaud <rjuju123@gmail.com> writes: > On Sun, Feb 20, 2022 at 12:31:22PM +0200, Markur Sens wrote: >> Is this documented & expected behavior or it’s just happens to work? > I don't think it's documented but it's an expected behavior, see > https://github.com/postgres/postgres/blob/master/src/backend/parser/parse_func.c#L57-L88 It is documented, near the bottom of this section: https://www.postgresql.org/docs/current/rowtypes.html#ROWTYPES-USAGE Other relevant oddities are mentioned in https://www.postgresql.org/docs/current/xfunc-sql.html#XFUNC-SQL-COMPOSITE-FUNCTIONS regards, tom lane