Why does my Postgres function always return the same value, regardless of input?

Question

I have two tables in a Postgres 15 database:

CREATE TABLE apps (
 id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
 created_at timestamp with time zone NOT NULL DEFAULT now(),
 updated_at timestamp with time zone NOT NULL DEFAULT now(),
 production_version_id bigint REFERENCES app_versions(id),
 development_version_id bigint REFERENCES app_versions(id),
 last_published timestamp with time zone
);

and

CREATE TABLE app_versions (
 id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
 created_at timestamp with time zone NOT NULL DEFAULT now(),
 app_id bigint NOT NULL REFERENCES apps(id),
 updated_at timestamp with time zone NOT NULL DEFAULT now(),
 published_date timestamp with time zone
);

I've created a function which is intended to return the published date of an app:

CREATE OR REPLACE FUNCTION generate_app_published_date (app_id apps.id%TYPE)
 RETURNS app_versions.published_date%TYPE
 LANGUAGE SQL
 SECURITY DEFINER
 SET search_path = public VOLATILE
 AS $$
 SELECT
 app_versions.published_date
 FROM
 app_versions
 LEFT JOIN apps ON apps.production_version_id = app_versions.id
WHERE
 apps.id = app_id;
$$;

However, this function returns the same result, regardless of the value I pass in:

SELECT generate_app_published_date (1);
 => 2023年04月01日 12:00:14.789647+00
SELECT generate_app_published_date (2);
 => 2023年04月01日 12:00:14.789647+00
SELECT generate_app_published_date (8675309); // not an actual app id
 => 2023年04月01日 12:00:14.789647+00

If I run the equivalent queries:

SELECT app_versions.published_date FROM app_versions LEFT JOIN apps ON apps.production_version_id = app_versions.id WHERE apps.id = 1;
 => 2023年04月01日 12:00:14.789647+00
SELECT app_versions.published_date FROM app_versions LEFT JOIN apps ON apps.production_version_id = app_versions.id WHERE apps.id = 1;
 => 2023年04月01日 12:11:00+00
SELECT app_versions.published_date FROM app_versions LEFT JOIN apps ON apps.production_version_id = app_versions.id WHERE apps.id = 8675309;
 => 

I cannot fathom what is happening here. It's a VOLATILE function, so it doesn't seem likely that it's a "cached" value.

Any suggestions?

Answer 1

app_id is both a parameter of the function and a column name in the query inside the function (app_versions.app_id), and the column name takes priority.

This is a common gotcha. One way of avoiding that it to use a naming convention for function parameters that distinguishes them from column names.