Pass in "Where" Parameters to Postgresql View

Pass In WHERE parameters to PostgreSQL View?

You could use a set returning function:

create or replace function label_params(parm1 text, parm2 text)
  returns table (param_label text, param_graphics_label text)
as
$body$
  select ...
  WHERE region_label = $1 
     AND model_id = (SELECT model_id FROM models WHERE model_label = $2)
  ....
$body$
language sql;

Then you can do:

select *
from label_params('foo', 'bar')

Btw: are you sure you want:

AND model_id = (SELECT model_id FROM models WHERE model_label = $2)

if model_label is not unique (or the primary key) then this will throw an error eventually. You probably want:

AND model_id IN (SELECT model_id FROM models WHERE model_label = $2)

Pass parameter to a View

Views can't have parameters, but you can wrap it into a function:

create function comedies(p_kind text)
  returns setof films
as
$$
 select *
  from films
  where kind = p_kind;
$$
language sql
stable;

Then use it like a table/view:

select *
from comedies('bla');

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 set a dynamic where in a view based on the user querying data?

For this specific use case, you could do something as simple as this:

create or replace view user_data AS 
  select * from users 
  where user_id = (select substring(current_user, length(current_user))::int);

However, this will return an error if the user's name does not end with something that can be turned into an int.

You may want to look into row level security for something a bit more robust and configurable.

How to pass values as parameters in sql query in geoserver view

It's working fine for me. We have to create view query like

select * 
from tablename 
ORDER BY ST_SetSRID(ST_Point(%longitude% , %latitude%), 4326) <-> geom 
LIMIT 10