Postgresql: is "public." an 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

Public is the default schema name.

For example, in MySQL it doesn't have schema (if I recall). Also, if you use another schema instead of public, your code will break.

http://www.postgresql.org/docs/8.3/interactive/ddl-schemas.html

I'd recommend using another variable name. There's probably another way too.

Answer 2

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.

Answer 3

The conflict happens because you used the same name for your variable and table name.

This is a very bad choice of naming, and can lead to many problems.

For example:

create function x (somename TEXT) returns bool as $$
declare
  tempbool int4;
begin
  select true INTO tempbool from some_table where somename = somename;
  return tempbool;
end;
$$ language plpgsql;

This code basically doesn't make any sense, because parser is not able to tell what "somename = somename" means. Same goes for table names (to some degree).

Generally you want your identifiers (table names, column names, variable names) to be unique.

I prefer to use "in_" prefix for arguments, but your choice of naming schema might be different.