Make "(composite).function_name" syntax work without search_path changes?

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.

Responses

Browse pgsql-general by date

  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