CODEWITHSUNDEEP
sqlfunctionpostgresqlalias
Sam Hood
Sam Hood

Reputation: 370

Column aliases from a function in PostgreSQL

I've only just started using postgreSQL (I've history with MSSQL and MySQL) and have a question about aliases. If I have the following SQL declared in a function:

RETURN QUERY SELECT "Table"."col1" AS "Column 1",
                    "Table"."col2" AS "Column 2"
             FROM   "Table";

Highlighting this and running it alone gives me what I would expect:

Column 1   |   Column 2
------------------------
Contents   |   Contents
of         |   of
col1       |   col2

However calling this function from outside seems to ignore the aliases that were set:

SELECT * FROM "f_Function"();

Gets:

col1       |   col2
------------------------
Contents   |   Contents
of         |   of
col2       |   col2

I tried changing the return type definition of the function in case they were of a higher naming priority to the parent process:

... RETURNS TABLE (col1 integer AS "Column 1", col2 integer AS "Column 2") ...

But this was syntactically wrong.

As I said I've just started using postgreSQL so might be missing something obvious, but I can't seem to find any help for this specific situation.

To be clear, I'm wanting to be able to call the function and have the column aliases appear in the returned table.

Any ideas?

EDIT: Solution from Yuri Levinsky

... RETURNS TABLE ("Column 1" integer, "Column 2" integer) ...

With

RETURN QUERY SELECT "Table"."col1",
                    "Table"."col2"
             FROM   "Table";

Returns table:

Column 1   |   Column 2
------------------------
Contents   |   Contents
of         |   of
col1       |   col2

Upvotes: 1

Views: 3780

Answers (1)

Yuri Levinsky
Yuri Levinsky

Reputation: 1595

Please use the following example: RETURNS TABLE (dstring character varying, ...) select * from your function_name;

You will see column names dstring,... as it specified in function declaration. You don't need to use alias: just name it as you wish.

Upvotes: 1

Related Questions