| From: | "Cyril B(dot)" <cbay(at)excellency(dot)fr> |
|---|---|
| To: | pgsql-general(at)postgresql(dot)org |
| Subject: | Constraint using a SQL function executed during SELECT |
| Date: | 2016-07-19 12:43:22 |
| Message-ID: | 87d685b8-f61e-a2b6-06e2-b9e8f69c90be@excellency.fr |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-general |
Hello,
Is there a way to execute a SELECT on a table having a constraint that
uses a non-working SQL function?
I know the easiest way would be to fix the function, but I'm the
database administrator, not the owner, and I need to do a COUNT(*) on
each table.
Here's a minimal use case:
CREATE TABLE t1 (
id integer
);
CREATE SCHEMA rename_me;
CREATE TABLE rename_me.t2 (
id integer
);
CREATE FUNCTION f(id integer) RETURNS boolean
LANGUAGE sql IMMUTABLE
AS $_$
SELECT true
FROM rename_me.t2
$_$;
ALTER TABLE ONLY t1 ADD CONSTRAINT c EXCLUDE (id WITH =) WHERE ((f(id)
IS NOT TRUE));
ALTER SCHEMA rename_me RENAME TO renamed;
SELECT * FROM "t1";
returns:
ERROR: relation "rename_me.t2" does not exist
LINE 3: FROM rename_me.t2
^
QUERY:
SELECT true
FROM rename_me.t2
CONTEXT: SQL function "f" during inlining
Tested on 9.5.2 (and an older 9.0).
--
Cyril B.
| From | Date | Subject | |
|---|---|---|---|
| Next Message | pbj | 2016-07-19 13:05:00 | Re: MongoDB 3.2 beating Postgres 9.5.1? |
| Previous Message | Teodor Sigaev | 2016-07-19 10:19:53 | Re: MongoDB 3.2 beating Postgres 9.5.1? |