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

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 ap

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.

StackExchange Database Administrators Q#325493, answer score: 3