run_sql for each row

[guspower]

Hi!

I have a .sql file which contains a number of different components (datagrid, timeline) that I would like to be able invoke multiple times via run_sql (or any other method) using row values from a result set as input. Is this possible?

I tried something like

SELECT
    'dynamic' as component, 
    sqlpage.run_sql('component/datagrid-with-timeline.sql?id=' || id) as properties
FROM
    input_table
    ;

but such an expression is invalid.

I saw a discussion about using recursive CTEs to achieve something similar but it doesn't seem to fit here. Essentially what I have is a "custom component" that is a set of standard components in a .sql that run given the supplied parameters. For now I'm going to put a table in and link off to each entry on a separate page, but it's not what I was aiming for.

Any input most welcome!

[lovasoa]

edit

Since SQLPage v0.25, it is possible to call run_sql on each row of a SQL result set, with

select 'dynamic' as component,
  sqlpage.run_sql('display_product.sql', json_object('product_id', product_id)) as properties
from products;

json_object is called json_build_object in postgres

original answer

Hi !
Thanks for raising this. Calling a sqlpage function iteratively on all request results is currently not possible. I want to implement this in the future, but it's quite a big change, and it will take a little time.

Yet, you have multiple options to get to the result you want:

1. You can use the embed property of the card component: https://sql.ophir.dev/documentation.sql?component=card#component
2. You can write a normal sql stored procedure that returns json and call SELECT 'dynamic' as component, my_function(id) as properties FROM input_table

If I were you, I would go with solution number 1.

[guspower]

Ah ok excellent, I did not know the card component has this capability - thank you!

[lovasoa]

I updated my answer above to reflect the latest changes in sqlpage.

[lovasoa]

I created an issue here to track the implementation of the solution to the initial problem: #337