postgresql query

Question

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.

What's wrong with the statement in your question?

Wayne Conrad
– Wayne Conrad

2011-05-25 02:51:01 +00:00
Commented May 25, 2011 at 2:51 — Wayne Conrad
– Wayne Conrad, Commented May 25, 2011 at 2:51

Brian L · Accepted Answer · 2011-05-25 02:52:16Z

3

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

answered May 25, 2011 at 2:52

Brian L

11k5 gold badges23 silver badges17 bronze badges

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

Comments

Andriy M · Accepted Answer · 2011-05-25 08:00:16Z

1

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)
)

answered May 25, 2011 at 8:00

Andriy M

78k18 gold badges100 silver badges157 bronze badges

Comments

Seth Robertson · Accepted Answer · 2011-05-25 03:20:26Z

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;

Collectives™ on Stack Overflow

postgresql query

Table1

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Table1

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related