Postgresql return table with default value

Question

I would like to create a function which returns with a table, but I would like to add default values to the values, like this:

CREATE OR REPLACE FUNCTION cleanDatabase()
RETURNS TABLE 
(
error TEXT,
createConnection TEXT,
dropDatabases TEXT DEFAULT 'OK',
dropTableSpaces TEXT,
closeConnection TEXT
)
AS $$
...

But I get the following error: ERROR: syntax error at or near "DEFAULT"

I use default keyword often when I create new tables and it is working fine. Is it just not a use case what I am doing here, and I should just create a temporary table with defaults and return with that one?

you can add coalesce in the select statement where you are returning the data — Anuraag Veerapaneni
– Anuraag Veerapaneni, Commented Nov 29, 2018 at 10:53

Bohemian · Accepted Answer · 2018-11-29 11:27:33Z

2

It doesn’t make sense to have a default for the return type’s columns, because you’re returning every column.

The default value for a table only kicks in when a row is inserted without providing values (as distinct from specifying a null) for columns with default values. There’s no way in PL/SQL to not return all values.

Use coalesce() or similar to provide a value when one can’t be determined from your SP logic.

answered Nov 29, 2018 at 11:27

Bohemian♦

427k103 gold badges603 silver badges750 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgresql return table with default value

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related