INSTALL scalarfs FROM community;
LOAD scalarfs;

LOAD scalarfs;

-- Read JSON from a variable
SET VARIABLE config = '{"debug": true, "port": 8080}';
SELECT * FROM read_json('variable:config');

-- Read CSV from inline content
SELECT * FROM read_csv('data+varchar:name,score
Alice,95
Bob,87');

-- Use a file path stored in a variable
SET VARIABLE data_path = '/data/reports/monthly.csv';
SELECT * FROM read_csv('pathvariable:data_path');

SET VARIABLE data_paths = ['/data/reports/monthly.csv', '/archive/reports/*.csv'];
SELECT * FROM read_csv('pathvariable:data_paths');

-- Write query results to a variable
COPY (SELECT * FROM my_table WHERE active) TO 'variable:exported' (FORMAT json);
SELECT getvariable('exported');

-- Store query results as native values (not serialized text)
COPY (SELECT path FROM files WHERE active) TO 'variable:paths' (FORMAT variable);
SELECT * FROM read_csv('pathvariable:paths');  -- Read all files from the list

-- Select which files to read via a catalog macro (pathmacro:)
CREATE TABLE reports AS
  SELECT * FROM (VALUES ('west', '/data/west.csv'), ('east', '/data/east.csv')) t(region, path);
CREATE MACRO region_files(p) AS (SELECT list(path) FROM reports WHERE region = p['region']);
SET allowed_pathmacro_macros = 'region_files';
SELECT * FROM read_csv('pathmacro:region_files?region=east');  -- reads only the east file

-- Build pathmacro: URLs safely (keys/values URL-encoded)
SELECT to_pathmacro_url('region_files', {region: 'west'});  -- pathmacro:region_files?region=west