From: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> |
---|---|
To: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Make "(composite).function_name" syntax work without search_path changes? |
Date: | 2017-10-30 22:48:35 |
Message-ID: | CAKFQuwasM3oA+A12rHYM0sMa7FU7aAPL2viQLLcViS2PEmZ8WQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
CREATE SCHEMA altschema;
CREATE TYPE altschema.alttype AS ( altid text, altlabel text );
CREATE FUNCTION altschema.label(item altschema.alttype)
RETURNS text
LANGUAGE sql
AS $$
SELECT (item).altlabel;
$$;
WITH vals (v) AS (
SELECT ('1', 'One')::altschema.alttype
)
SELECT (v).label
FROM vals;
-- column "label" not found in data type altschema.alttype
SET search_path TO altschema;
WITH vals (v) AS (
SELECT ('1', 'One')::altschema.alttype
)
SELECT (v).label
FROM vals;
-- success
The system knows that the datatype being inspected is "altschema.alttype" -
would it be reasonable for the system to check for a function named "label"
in the same schema as the target type, "altschema", with the target
argument type and invoke it if present?
At this point I'm just writing: altschema.label(v) which is adequate but
not as clean. I'm consciously trying to write queries that don't require
application schemas in the search path: including the joyous
operator(altschema.@@) syntax in some situations. I suppose inference
could be considered in that situation as well.
David J.
From | Date | Subject | |
---|---|---|---|
Next Message | rakeshkumar464 | 2017-10-30 22:48:57 | pgaduit - is there a way to audit a role |
Previous Message | rakeshkumar464 | 2017-10-30 22:44:14 | Re: pg_audit to mask literal sql |