[GENERAL] User-defined operator function: what parameter type to use for uncast character string?
(Cross-posted to StackOverflow:
http://stackoverflow.com/questions/25041100/postgresql-user-defined-operator-function-what-parameter-type-to-use-for-uncast
)
I'm defining my own domain and a equality operator. I cannot cause
PostgreSQL to use my operator function in a query without explicitly
casting a character-string argument to the type I used in defining the
operator. My question is how to cause my custom operator to be used
without the cast.
As an example: first I define a domain and a table column of that
type. For this example, the type requires its values to be three
uppercase letters A, B or C, and the equality operator will cause
queries to match regardless of case.
CREATE domain my_domain as char(3) check(VALUE similar to '[A-C]{3}');
CREATE TABLE my_table (val my_domain);
INSERT INTO my_table VALUES ('ABC');
The type of the column is my_domain:
sandbox=> \d my_table
Table "public.my_table"
Column | Type| Modifiers
+---+---
val| my_domain |
Before defining the custom equality operator, case-sensitive queries
work as I expect. The row in the table is capital letters, so the
query must contain capital letters to match the row
sandbox=> SELECT * FROM my_table WHERE val='abc';
val
-
(0 rows)
sandbox=> SELECT * FROM my_table WHERE val='ABC';
val
-
ABC
(1 row)
Next I create an equality operator to do case-insensitive matching:
CREATE FUNCTION my_equals(this my_domain, that text) RETURNS boolean AS
'SELECT CAST (this AS text) = upper(that)' LANGUAGE SQL;
CREATE OPERATOR = (procedure=my_equals, leftarg=my_domain, rightarg = text);
The new operator is invoked causing a query containing lowercase
letters to match the uppercase column value, but only if I cast the
type of the WHERE clause:
sandbox=> SELECT * FROM my_table WHERE val=CAST ('abc' AS text);
val
-
ABC
(1 row)
sandbox=> SELECT * FROM my_table WHERE val='abc';
val
-
(0 rows)
Question: What can I do so my custom equality operator is used without
the cast? In other words, how to cause the last query above return
the table row (without changing the query)? I have tried defining
my_equals() so its second parameter type is either varchar and
char(3), but those still require a cast in the WHERE-clause of the
query. I've also tried anyelement, but that does not work even with a
cast.
Thank you,
--
Adam Mackler
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Re: User-defined operator function: what parameter type to use for uncast character string?
On Wed, Jul 30, 2014 at 10:59:28PM -0700, David G Johnston wrote: > ISTM that if this was supported you would be doing it correctly. Thank you for the quick response. I'm not understanding you. Could you elaborate? > The main problem is you are abusing DOMAIN - which is strictly the > base type with constraints - and trying to add operators specific to > the DOMAIN (i.e., ones that would not work with the base type). Can you explain what the abuse is? Also why the "=" operator does not work even without the domain? If I do everything the same, but make the column type "char(3)" rather than "my_domain", then still I need to cast the literal in the query to "text". I'm not understanding (1) why postgres doesn't use the type of the column--either char(3) or my_domain--and then choose the operator function that has that type for its first paramater even where the second is "unknown", and (2) why using the cast "WHERE val='abc'::text" makes it work. It seems as if the determining factor is the type of the literal in the WHERE_clause. > And so now you have "domain = unknown" and the system is trying to > figure out what unknown should be and also which operator to pick > and it decides that since =(text,text) covers the domain and the > unknown that is what it will pick. If =(text,text) is chosen where the second argument is "unknown" rather than "text", then postgres has no problem deciding that a parameter defined for "text" will handle "unknown", right? So if I define =(my_domain,text) or =(char(3),text) then wouldn't those be preferable to =(text,text) where the first argument type can be known from the type of the column (as defined in the table) used as the first argument to the operator function? If I give my operator the unique name "" then my operator function is chosen without the cast. That makes it seem like a matter of priority, where the built-in "=" operator takes priority over my user-defined one. If postgres will accept my operator as being appropriate, isn't there some way to give it priority over the built-in =(text,text) operator? In other words, "\d my_table" shows the type of the column as "my_domain" or "char(3)" depending on how I define it. So why isn't that taken into acount when choosing the operator function when the second argument is "unknown"? > Maybe you should consider using an "enum" I don't see how I can use an enum. Wouldn't that require defining all possible cominations, which even in this limited example would be 26^3 values? I just used three uppercase letters (and case-insensitive matching) as an example. In my actual application I have twelve characters and the operator function is doing more advanced regular-expression matching. Thanks again, -- Adam Mackler -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Re: User-defined operator function: what parameter type to use for uncast character string?
On Thu, Jul 31, 2014 at 10:03:00AM -0400, Tom Lane wrote: > 2. text is the preferred type among the string class, so any case where > you have text on one side and some other string type on the other is > going to get resolved as text vs text. > Because of #1, domain-specific functions and operators tend to be pretty > useless; you find yourself always having to cast the other side to get > > If you're intent on having this behavior, the way to go at it is to make > your own actual datatype (not a domain) and create all your own comparison > operators for it. You can add an implicit cast to text for cases where Thanks to everyone who responded to my question. Yes, the case-insensitivity was an example contrivance. My actual operator function is using regular expressions to do text replacements, so citext is not going to solve all my problems. Looks like 'CREATE TYPE' is the only way to get what I'm after here, and since there are regular expressions involved, a C-language solution is not seeming like it's going to be very convenient. On top of that, the content of my regular-expression replacement strings are constructed from data in my database, so really my SQL-language operator function seemed ideal except--of course--for the limitations you all have explained to me. One final question: the 'CREATE CAST' command got my interest. I'm assuming that when the docs say it 'performs a conversion between two data types,' that the meaning of "data type" includes only those created using 'CREATE TYPE' and excludes domains. If I am mistaken on that point I would be grateful to learn of that mistake. Thanks again, -- Adam Mackler -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Conflicting function name in dynamically-loaded shared library
Hi: I am wanting to define some functions as described in section 35.9 of the manual, "C-Language Functions." I am compiling pre-existing files of c-code from another project into object files, and then linking those object files into a shared library along with my own functions that follow the posgresql calling convention, invoking other functions as desired. This pre-existing c-code I'm compiling happens to contain a function named "point_add()". I see the postgresql source file fmgrtab.c also has a function with the same name. When my code tries to invoke its version of point_add() I get a bus error, and when I changed the name of that function to something else the bus error went away. Of course, since I'm working with source code I can just keep the modified function name, but I would like to be able to keep my version of this c code updated with that project and so to use it unmodified. So my questions are: first, might I be wrong about the cause of this bus error? I cannot think of another reason why changing the name of a function would have this effect, but maybe there's some other reason besides the "point_add()" function in fmgrtab.c conflicting. If so, I'm interested to know. If, however, it is probable that this bus error is a result of this naming conflict, then do I have any options for working around it that would enable me to use the code from this other library without changing the name of its "point_add()" function? I know I could ask that project's developers to change the function's name, but that could break other code that currently uses it, and even if it didn't, I would prefer something less intrusive on that project. Thanks very much for any ideas about this, -- Adam Mackler -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Conflicting function name in dynamically-loaded shared library
On Wed, Jan 28, 2015 at 11:23:15AM +, Albe Laurenz wrote: > If you are on Linux, you could try the following patch: > > http://www.postgresql.org/message-id/ca+csw_tpdygnzcyw0s4ou0mtuouhz9pc7mrbpxvd-3zbiwn...@mail.gmail.com > > ... > Renaming one of the functions seems like the best thing to do. I am using FreeBSD, so RTLD_DEEPBIND is not supported by my dlopen(). However, your suggestion to rename the function seems at least as good. Patching PostgreSQL would just mean trading difficulties when upgrading the third-party library for difficulties when upgrading PostgreSQL. Thank you for the response and insight. -- Adam Mackler -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] pg_dump recording privileges on foreign data wrappers
If I grant a privilege on a foreign data wrapper like this: GRANT USAGE ON FOREIGN DATA WRAPPER postgres_fdw TO myuser; from within psql, then a dump of the database produced using pg_dump seems to lack an equivalent GRANT statement, even though it contains the CREATE EXTENSION statement for that foreign data wrapper. Am I usderstanding correctly that when I feed that output of pg_dump back into psql it will result in a database that has the foreign data wrapper but without the priviliges that were set in the database that was dumped? Is that really what is supposed to happen? Is there a way to get pg_dump to output the necessary statements such that running the dump back through psql results in the same priviliges that I started with? I am using version 9.5.6. Thanks very much, -- Adam Mackler -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Can a view represent a schedule for all days into the future?
Hi: I recently posted a question on stackoverflow, but I suspect it may require specific PostgreSQL knowledge, so I'm cross-posting a reference to it here. http://stackoverflow.com/questions/19237450/can-sql-view-have-infinite-number-of-rows-repeating-schedule-each-row-a-day The gist is that I have information representing companies' daily schedules in some tables, and I want to be able to do a SELECT to get the business hours for any given day, arbitrarily far into the future (and past, but I can live with a limit in that direction). I want to encapsulate any messy SQL in a single location where it can be hidden from client usage, presumably in a view. I currently have a user-defined function that returns the results I want, but the problem is in the invocation: Some host-language client libraries aren't so graceful with user-defined functions, especially when they return multiple rows of multiple columns. I would like to be able to do a simple table-style SELECT query, presumably on a view. But if I can put any date into the WHERE clause, then that means the view would represent a table with an infinite number of rows, would it not? The posting on SO clarifies the specifics of what I'm trying to. It seems like there ought to be a way, but I haven't figured it out. Thanks very much. -- Adam Mackler -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Can a view represent a schedule for all days into the future?
On Thu, Oct 10, 2013 at 10:42:47AM -0700, David Johnston wrote: > Adam Mackler-3 wrote > > http://stackoverflow.com/questions/19237450/can-sql-view-have-infinite-number-of-rows-repeating-schedule-each-row-a-day > > > Not sure how you can state "But I'm willing to agree never to query such a > view without a WHERE clause that restricts the number of rows." when you > cannot even guarantee which host-language client libraries you need to > support. I'm willing to agree that if I ever query such a view without a WHERE clause that restricts the number of rows, then I won't blame anyone but myself for the consequences. If someone can answer this challenge without imposing that requirement, then I shall be all the more impressed. The library I'm using is SLICK: http://slick.typesafe.com/ As far as I can tell it can't handle user-defined functions that return multiple rows nor multiple columns, except perhaps through some low-level SQL interface that would defeat my idea of the purpose of using a database interface library. As I mention in the post on SO, my understanding of relational database theory tells me that tables/relations ARE functions, in the sense of being a mapping between a primary key and a row. So there's nothing ambiguous about issuing a SELECT query for some particular date, and if I'm only querying for one date, then there's only one day in the resulting table. It wouldn't surprise me if there were some way to create a view based on underlying tables that contain schedules for each day of the week that I could query in such a fashion for any day arbitrarily far into the future. If this is possible, then I would be interested in knowing what the creation of such a view looks like, independently of my immediate practical needs. Call me academic. -- Adam Mackler -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] A view representing a schedule for all days into the future
Hi: I recently posted a question on stackoverflow, but I suspect it may require specific PostgreSQL knowledge, so I'm cross-posting a reference to it here. http://stackoverflow.com/questions/19237450/can-sql-view-have-infinite-number-of-rows-repeating-schedule-each-row-a-day The gist is that I have information representing companies' daily schedules in some tables, and I want to be able to do a SELECT to get the business hours for any given day, arbitrarily far into the future (and past, but I can live with a limit in that direction). I want to encapsulate any messy SQL in a single location where it can be hidden from client usage, presumably in a view. I currently have a user-defined function that returns the results I want, but the problem is in the invocation: Some host-language client libraries aren't so graceful with user-defined functions, especially when they return multiple rows of multiple columns. I would like to be able to do a simple table-style SELECT query, presumably on a view. But if I can put any date into the WHERE clause, then that means the view would represent a table with an infinite number of rows, would it not? The posting on SO clarifies the specifics of what I'm trying to. It seems like there ought to be a way, but I haven't figured it out. Thanks very much. -- Adam Mackler -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Views versus user-defined functions: formatting, comments, performance, etc.
Hi: I notice when I save a view, I lose all the formatting and comments. As I was writing a complicated view, wanting to retain the format and comments, I thought I could just save it as a function that returns a table value. A function would evaluate to the same value as a view, but changing it later might be less confusing. However, I'm guessing (since I don't know anything about the internals) that the loss of formatting and comments is a result of the view being processed and stored in a more computer-friendly format, while functions are simply stored as the text that I type. That gives me reason to suspect there may be performance or other differences between the same SQL statement stored either as a view or a user-defined function. So that's my question: as someone who doesn't have a problem with putting a pair of empty parentheses at the end of a table variable name, what factors should I be thinking of while deciding whether to store my self-composed, multi-hundred-line long SQL statement as a view or a function? -- Adam Mackler -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Can a view use a schema search_path?
I have the feeling the answer is no, but I would like an authoritative answer before I give up. My plan was to have two schemas: one for the live data, and one for staging, training, and testing. Both schemas would have identically-named tables. I wanted to create a single view in the public schema, and have that one view refer to the tables in one or the other schema depending on my search_path setting at the time I query the view. But it seems that at the time the view is created it decides which schema's table it's referring to, even if I don't explicitly qualify the table names with the schema name. Am I correct in concluding that there's no way to have a single view in the public schema that selects data from tables in different other schemas depending on my search_path at the time I execute a query involving that view? Thanks. -- Adam Mackler
