SQLite Forum

Joining table-valued functions with a dynamic constraint

Joining table-valued functions with a dynamic constraint

(1) By Patrick DeVivo (patrickdevivo) on 2020-12-14 00:52:39 [link] [source]

Hi there,

I hope it's appropriate to ask this question here. I currently have two eponymous only virtual tables defined that are used as table-valued functions. One takes one parameter, the other takes two.

I'd like to join the two tables, such that one of the columns in the result set of the first table is used as one of the parameters/constraints in the second.

The functions look like: github_org_repos('ORG_NAME') and github_pull_requests('ORG_NAME', 'REPO_NAME'), I'd like a query along the lines of:

SELECT * FROM github_org_repos('ORG') repo
JOIN github_pull_requests('ORG') pr
ON pr.repo_name = repo.name

or similar

Where repo_name is the constraint in question of the github_pull_requests table.

Currently, I seem unable to execute this query, as the github_pull_requests doesn't appear able to recognize the repo_name constraint (which is the second parameter). I'm not sure if it's a problem with the virtual table implementation, or perhaps a limitation of how table-valued functions are meant to work.

If anyone has pointers in the right direction, I would really appreciate any input!

FWIW, the implementation is in Go using the available C bindings

(2) By Gunter Hick (gunter_hick) on 2020-12-14 07:10:08 in reply to 1 [source]

I would recommend not mixing up passing parameters and constraints.

select * from github_pull_requests('ORG',github_org_repos('ORG'));


select p.* from github_org_repos r join github_pull_requests p on r.org = p.org and r.name = p.repo_name where r.org = 'ORG';

(3) By Keith Medcalf (kmedcalf) on 2020-12-14 09:38:02 in reply to 1 [link] [source]

You probably need to ensure the join order by using "cross join" if the function is not well designed.

Do you know for a fact that the virtual tables actually are returning the data you want to see under the conditions under which you are calling them?

(4) By Richard Hipp (drh) on 2020-12-14 12:46:52 in reply to 1 [link] [source]

I would have thought that would work. What error message are you getting?

Did you try the following:

  FROM github_org_repos('ORG') repo, github_pull_requests('ORG',repo.name) pr;

(5) By Patrick DeVivo (patrickdevivo) on 2020-12-27 16:06:20 in reply to 1 [link] [source]

Thank you all for the response, after some further digging it appears to have been a problem with the virtual table implementation. The "expected" behavior now seems to be working. Thank you to those who provided more context!