using output from a function as a qual?

[judell]

For the Twitter plugin we give two examples of ways to convert a username to the required user_id.

1. id via subselect

select
  id,
  text
from
  twitter_user_tweet as t
where
  t.user_id in (select id from twitter_user where username = 'steampipeio')

2. id via join

select
  t.id,
  t.text
from
  twitter_user_tweet as t,
  twitter_user as u
where
  t.user_id = u.id
  and u.username = 'steampipeio'

I would rather do this:

3. id via function

select
  id,
  text
from
  twitter_user_tweet
where
  user_id = twitter_userid_for_username('steamipeio')

where twitter_userid_for_username() is defined as, e.g.:

create or replace function twitter_userid_for_username (_username text) returns text as $$
  declare user_id text;
  begin
    select
      id
    from
      twitter.twitter_user
    where
      username = _username
    into
      user_id;
    return user_id;
  end;
$$ language plpgsql;

This fails with:

Internal desc = 'List' call is missing required quals: column:'user_id' operator: =

It's possible to rewrite the function to be set-returning:

create or replace function twitter_userid_for_username (_username text) 
  returns table (
    user_id text
  ) as $$
  begin
    return query
      select
        id
      from
        twitter.twitter_user
      where
        username = _username;
    end;
$$ language plpgsql;

and then do this:

select
  id,
  text
from
  twitter_user_tweet
where
  user_id in ( select user_id from twitter_userid_for_username('steampipeio') )

but that loses the convenience, and economy of expression, that is the point of #3.

discussion

This is just one example of ways that functions could make queries easier to write and read. In the case of the Twitter API, the info you have (a username) will in many contexts need to be converted into the info the API needs (a user_id). Methods #1 and #2 impose a higher cognitive load than #3. As a user of a plugin, I'd appreciate not only being able to write functions that enable #3, but even more, having the most common/useful such functions provided by the plugin install.

For the purposes of this issue, though, the question for @kaidaguerre is only: what prevents the function from being used to provide a value for the qual? From a superficial Postgres perspective, these look the same.

 select pg_typeof ( ( select id from twitter_user where username = 'steampipeio' ) )

+-----------+
| pg_typeof |
+-----------+
| text      |
+-----------+

 select pg_typeof ( twitter_userid_for_username('steampipeio') )

+-----------+
| pg_typeof |
+-----------+
| text      |
+-----------+

Is there a restriction that could be easily/safely relaxed, or do dragons lurk there?