2

Attribute notation function call gives error when current schema is different from one of function.

I have created a function 

CREATE FUNCTION pub.FullName(pub.reps)
  RETURNS text AS
$func$
       select ($1.fname || ' ' || $1.lname)
$func$ LANGUAGE SQL;

I am trying to call the function with attribute notation as described in docs):

select r.fullname from pub.reps r;

But get an error message:

ERROR:  column "fullname" does not exist

Query with functional notation works fine:

select pub.fullname(r.*) from pub.reps r;

Database has been migrated from PostgreSQL 10 with backup/restore.

Select version() gives: PostgreSQL 11.3, compiled by Visual C++ build 1914, 64-bit

UPD. Found out that if I set pub schema as default, then select r.fullname from pub.reps r works with no error.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Leo
  • 420
  • 3
  • 18
  • test database on which server? why the mixed case spelling? did you use double qoutes anywhere? – Erwin Brandstetter Jun 29 '19 at 11:20
  • Mixed case for readability only. It does not affect the result. Server is the same as for the first database - my own laptop. – Leo Jun 29 '19 at 13:34
  • I've found out that it depends on current schema. If I set up pub schema as default, then `select r.FullName from pub.reps r` works. Otherwise I get an error. – Leo Jun 29 '19 at 13:59
  • @ErwinBrandstetter, thank you for detailed answer and for improving my post. Now it looks much better. Also for db<>fiddle link. Which should be a good tool. As I understand there is no way to use attribute functionality without modifying search path (for example with explicitly using schema in query). So I would better wait for Postgres 12. – Leo Jun 30 '19 at 08:05
  • 1
    The schema of the function has to be in the search path as there is no way to schema-qualify the attribute (turned into a function). Strictly speaking, you do not *have* to modify the `search_path`: you can always create the function in a schema that's already there, `pg_catalog` as a measure of last resort. (Though I'd rather avoid putting user objects there.) – Erwin Brandstetter Jul 01 '19 at 00:32

1 Answers1

2

You found the root of the problem yourself. To be precise: the schema of the function pub has to be listed anywhere in the current search_path, does not have to be the "default" or "current" schema (the first one in the list). Related:

So Postgres did not find the function. Postgres 11 isn't different from Postgres 10 in this regard. There are some noteworthy, related developments, though. You mentioned:

Database has been migrated from PostgreSQL 10 with backup/restore.

Consider this subtle change pointed out in the release notes for Postgres 11:

  • Consider syntactic form when disambiguating function versus column references (Tom Lane)

    When x is a table name or composite column, PostgreSQL has traditionally considered the syntactic forms f(x) and x.f to be equivalent, allowing tricks such as writing a function and then using it as though it were a computed-on-demand column. However, if both interpretations are feasible, the column interpretation was always chosen, leading to surprising results if the user intended the function interpretation. Now, if there is ambiguity, the interpretation that matches the syntactic form is chosen.

So, if there was a column fullname in table reps and also the function pub.fullname(pub.reps) you display, Postgres 10, even with functional notation, would still chose the column:

SELECT fullname(r) FROM reps r;  -- resolves to column if it exists, ignoring function

db<>fiddle here for Postgres 10

Postgres 11 (more reasonably) choses the function:

db<>fiddle here for Postgres 11

Postgres 12 (currently beta) eventually implements true generated columns. The release notes:

  • Add support for generated columns (Peter Eisentraut)

The content of generated columns are computed from expressions (including references to other columns in the same table) rather than being specified by INSERT or UPDATE commands.

Only STORED generated columns made it into this release, though. The (more interesting IMO) VIRTUAL variant was postponed for a later release. (Not in Postgres 13, yet.)

Your table could look like this:

CREATE TABLE pub.reps (
  reps_id  int GENERATED ALWAYS AS IDENTITY PRIMARY KEY 
, fname    text NOT NULL
, lname    text NOT NULL
, fullname text GENERATED ALWAYS AS (fname || ' ' || lname) STORED
);

db<>fiddle here

I declared fname and lname columns NOT NULL. Else, your simple concatenation (fname || ' ' || lname) is a trap. See:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228