CODEWITHSUNDEEP
sqlpostgresql
Rod
Rod

Reputation: 1

postgresql query

I have a table with one column (t1date) as:

Table1

t1date  
----------  
2011-05-24

There is also a function which takes one parameter dfunction(fdate). I want to pass the value of the t1date to the function. Something like:

SELECT * 
  FROM dfunction(SELECT t1date 
                   FROM Table1 
               ORDER BY t1date 
                  LIMIT 1)

which is equivalent to

SELECT * 
  FROM dfunction('2011-05-24')

I don't want to write another function for this. What I'm looking for is to use the select statement. Is it possible? If so how.

Upvotes: 0

Views: 100

Answers (3)

Andriy M
Andriy M

Reputation: 77677

I understand, a subquery, when used as a scalar expression, should be enclosed in parentheses. So, how about this:

SELECT * 
FROM dfunction(
   (SELECT t1date 
      FROM Table1 
  ORDER BY t1date 
     LIMIT 1)
)

Upvotes: 1

Seth Robertson
Seth Robertson

Reputation: 31451

I'm having difficulty understanding exactly what you want. I have a few options, perhaps you can explain more if neither of these are what you want?

create table foo1 ( t1 date, id int );
insert into foo1 values ('2001-01-01', 1), ('2002-01-01', 2),  ('2003-01-01', 3),  ('2004-01-01', 4);

create or replace function foo1(date) returns setof foo1 as $$ select * from foo1 where $1 > t1 order by t1 asc; $$ language 'sql';
select * from foo1('2002-02-02');

create or replace function foo2(text) RETURNS SETOF foo1 as $$
DECLARE
 q alias for $1;
 r foo1%rowtype;
BEGIN
 for r in execute q loop
  return next r;
 end loop;
 return;
END;
$$ language 'plpgsql';
select * from foo2('select * from foo1 order by t1 asc limit 1');
drop table foo1 cascade;

Upvotes: 0

Brian L
Brian L

Reputation: 10955

select dfunction(t1date) from Table1 order by t1date asc limit 1;

Upvotes: 3

Related Questions