Is "public." a standard way to fully qualify table name in PostgreSQL?

Question

If I don't qualify a public. on account_category table, out account_category will have a conflict with account_category table name.

Does public. also works on other RDBMS?

CREATE OR REPLACE FUNCTION X_RAIN(x VARCHAR, OUT user_id VARCHAR, out account_category varchar, out depth int) returns setof record
AS 
$$
BEGIN
        return query 
        select uar.account_id, c.account_category, c.depth
        from account_with_types_chart_of_account uar
        inner join public.account_category c using(account_category_id);
END;
$$ LANGUAGE 'plpgsql';

Answer 1

Regarding public in PostgreSQL, public is defined as the default schema name when no schema name is specified. However, this can changed in the postgresql.conf file on the search_path = xxx line. To see what your current default schemas are set to issue the following SQL command:

SHOW_ search_path;

If you want to change your default schema path in your open query session, issue the following SQL command:

SET search_path = new_path;

However, in the example you posted I believe that the naming conflict you are having problems with is not with the schema name but with the function parameter name account_category and the table name account_category. You could rename your parameter name to avoid this conflict. In databases with many schemas, for clarities sake I often explicitly specify public at the start of database object names.

Regarding your second question, I don't think PostgreSQL is unique in its usage of public, but I do know that many other databases do their schemas in a different way.