Table as an Argument of a Postgresql Function

Table name as a PostgreSQL function parameter

Before you go there: for only few, known tables names, it's typically simpler to avoid dynamic SQL and spell out the few code variants in separate functions or in a CASE construct.

That said, what you are trying to achieve can be simplified and improved:

CREATE OR REPLACE FUNCTION some_f(_tbl regclass, OUT result integer)
    LANGUAGE plpgsql AS
$func$
BEGIN
   EXECUTE format('SELECT (EXISTS (SELECT FROM %s WHERE id = 1))::int', _tbl)
   INTO result;
END
$func$;

Call with schema-qualified name (see below):

SELECT some_f('myschema.mytable');  -- would fail with quote_ident()

Or:

SELECT some_f('"my very uncommon table name"');

Major points

Use an OUT parameter to simplify the function. You can directly select the result of the dynamic SQL into it and be done. No need for additional variables and code.

EXISTS does exactly what you want. You get true if the row exists or false otherwise. There are various ways to do this, EXISTS is typically most efficient.

You seem to want an integer back, so I cast the boolean result from EXISTS to integer, which yields exactly what you had. I would return boolean instead.

I use the object identifier type regclass as input type for _tbl. That does everything quote_ident(_tbl) or format('%I', _tbl) would do, but better, because:

• .. it prevents SQL injection just as well.

• .. it fails immediately and more gracefully if the table name is invalid / does not exist / is invisible to the current user. (A regclass parameter is only applicable for existing tables.)

• .. it works with schema-qualified table names, where a plain quote_ident(_tbl) or format(%I) would fail because they cannot resolve the ambiguity. You would have to pass and escape schema and table names separately.

It only works for existing tables, obviously.

I still use format(), because it simplifies the syntax (and to demonstrate how it's used), but with %s instead of %I. Typically, queries are more complex so format() helps more. For the simple example we could as well just concatenate:

EXECUTE 'SELECT (EXISTS (SELECT FROM ' || _tbl || ' WHERE id = 1))::int'

No need to table-qualify the id column while there is only a single table in the FROM list. No ambiguity possible in this example. (Dynamic) SQL commands inside EXECUTE have a separate scope, function variables or parameters are not visible there - as opposed to plain SQL commands in the function body.

Here's why you always escape user input for dynamic SQL properly:

db<>fiddle here demonstrating SQL injection

_{Old sqlfiddle}

Pass table to postgreSQL function, execute select statement, return table

You can not pass a "table" as a function argument. You can pass the name of a table to a PL/pgSQL function and then run a dynamic query:

CREATE OR REPLACE FUNCTION summing_function(input_table text)
    RETURNS TABLE (col_1 int, col_2 int, col_3 int, col_4 int) AS $$
BEGIN
    RETURN QUERY EXECUTE
        format('SELECT *, col_1 + col_2 + col_3 FROM %I', input_table);
END;
$$ LANGUAGE plpgsql;

How do I pass in a table parameter to this function?

Everything tested in Postgres 9.4.

Postgres has some weak spots in the syntax for handling ROW types. You cannot cast from a table (alias) directly:

SELECT w::waypoint FROM waypoints w;

ERROR:  cannot cast type waypoints to waypoint

The solution is only one step away: decompose the row in a subquery, then the cast works. This way, column values are decomposed and wrapped into the new type directly, without casting to text and back. No need to list all columns individually and you don't need to create a custom cast, either:

SELECT (w.*)::waypoint FROM (SELECT * FROM waypoints) w;

Or shorter:

SELECT w.*::waypoint FROM (TABLE waypoints) w;

Or shorter, yet:

SELECT w::waypoint FROM (TABLE waypoints) w;

• Is there a shortcut for SELECT * FROM?

SQL Fiddle

That's shorter and faster, in a quick test with 30k rows and simple types 10x faster than casting to text and back. If you have (big) jsonb columns or any complex type (expensive conversion to/from text), the difference will be much bigger, yet.

More importantly, you don't need another custom composite (ROW) type. Every table already has its row defined as type automatically. Just use the existing type waypoints instead of waypoint (if at all possible). Then all you need is:

SELECT w FROM waypoints w;

Or, for your example:

SELECT everything(t) FROM temp t;  -- using type waypoints
SELECT everything(t::waypoint) FROM (TABLE temp) t;  -- using type waypoint

Asides:

• A table does not have "arguments" but columns.

• You are not passing a table parameter to this function, but rather a row value. That's how you pass a table by name:

  • Table name as a PostgreSQL function parameter

  You can't "pass a whole table" as parameter directly in Postgres, there are not table variables. You would use a cursor or a temp table for that.

Function

Your function has an invalid type declaration and is needlessly complex. I seriously doubt you want to create a view:

CREATE FUNCTION everything(_wp waypoint)  -- or use type waypoints
  RETURNS TABLE(node int, xy text[]) AS
$func$
BEGIN
   RETURN QUERY
   SELECT ...
END
$func$ LANGUAGE plpgsql;

text array is not valid syntax, using text[] instead to declare an array of text.

Rather not use the table / type name waypoints as function parameter name, that opens you up to confusing errors.

Or just use a simple SQL function if your case is as simple as demonstrated:

CREATE FUNCTION everything(_wp waypoint)  -- or use type waypoints
  RETURNS TABLE(node int, xy text[]) AS
$func$
   SELECT ...
$func$ LANGUAGE sql;

Don't quote the language name. It's an identifier.

How to select from a Postgresql function that requires a table as argument

This work for me:

SELECT (func(t.*)).* FROM tab1 AS t WHERE t.id = 1

You could use LATERAL JOIN too:

SELECT t.*
FROM tab1,
      LATERAL func(tab1) AS t
WHERE tab1.id = 1;

Passing table name to a function and returning a table

If a function returns a scalar value it must contain return <expression>:

• declare a variable,
• assign a value to it (in the example with execute ... into ...),
• use it in return <expression>.

Example:

drop function if exists foo(_t text);
create or replace function foo(_t text)
returns bigint as
$func$
declare
    res bigint;
begin
    execute format('select count(*) from %I', _t) into res;
    return res;
end
$func$ language plpgsql;

select foo('films');

If a function returns a table it must contain return next or return query:

• assign a value to a column of declared return table,
• use return next,
• call the function in FROM clause, not in SELECT list.

Example 1:

drop function if exists foo(_t text);
create or replace function foo(_t text)
returns table(count bigint) as
$func$
begin
   execute format('select count(*) from %I', _t) into count;
   return next;
end
$func$  language plpgsql;

select * from foo('films');

If a function returns a table you can also use return query:

Example 2:

drop function if exists foo(_t text);
create or replace function foo(_t text)
returns table(count bigint) as
$func$
begin
    return query
        execute format('select count(*) from %I', _t);
end
$func$  language plpgsql;

select * from foo('films');

Can a function taking a table as parameter be declared as IMMUTABLE?

If you are concerned about performance, you should make that a language sql function so it can be inlined

CREATE FUNCTION isvisible(p_row clients) 
  RETURNS BOOLEAN
AS $BODY$
  select p_row.active AND NOT p_row.deceased;
$BODY$ 
LANGUAGE sql 
STABLE;

Even if it's not inlined, a SQL function is still more efficient then a PL/pgSQL function (see e.g. here)

---

As pointed out by Laurenz Albe in the comments, it is safe to mark that function immutable.

Postgresql function with values (from another table) as arguments

Enclose the SELECT statements in parentheses to make them expressions like this:

select * from interval_generator(
    (SELECT datetime FROM lookup Order By datetime limit 1),
    (SELECT datetime FROM lookup Order By datetime Desc limit 1),
    '1 hours'::interval
);

Please note that

SELECT datetime FROM lookup Order By datetime limit 1

is exactly

SELECT min(datetime) FROM lookup

which seems to me better readable. As the function body of interval_generator comprises of a single SQL query why don't you make it a plain SQL function instead of pl/pgsql?

<your-function-declaration> as $$
 SELECT
   (n) time_start,
   (n + round_interval) time_end
 FROM generate_series(date_trunc('minute', dt_start), dt_end, round_interval) n;
$$
LANGUAGE 'sql';

---

Related Topics