sqlite-eval-params.pl

#!/usr/bin/perl
use strict;
use warnings;
use DBI;
use JSON;
use YAML::XS;

my $dbh = DBI->connect('dbi:SQLite:dbname=:memory:');

our $Arg;

$dbh->sqlite_create_function('param', 0, sub {

  return $Arg;
  
});

$dbh->sqlite_create_function('eval', 2, sub {

  my ($sql, $arg) = @_;

  local $Arg = $arg;
  my $result = $dbh->selectall_arrayref($sql, { Slice => {} });

  return JSON->new->encode($result);
  
});

$dbh->do(q{
  CREATE VIEW view_with_param AS
  SELECT 'first row' AS name, param() AS param
  UNION ALL
  SELECT 'second row' AS name, param() AS param
});

print Dump (
  $dbh->selectall_arrayref(q{
    SELECT * FROM (SELECT eval(
      'SELECT * FROM view_with_param',
      '[1,2,3]'
    ))
  })
);