Use psql variables for "want" values?

[Rory Campbell-Lange]

This is my first posting here. Thanks very much to David for pgtap which our
team uses extensively.

This question isn't specifically about pgtap, but I'd be grateful for some help.

I have a test along the following lines:

SELECT results_eq (
$$
SELECT
a, b, c, d, e
FROM
test_function(param1, param2...)
$$
,
$$
VALUES(1, -1, -1, 1, -1)
$$
,:'testname'
);

which works great.

However if I could use a psql variable for the "VALUES..." statement it may
help make the tests more readable by others by, for example, defining the test
name and expected results ahead the test function call, eg:

\set testname "test 33: description'
\set want VALUES(1, -1, -1, 1, -1)

Trying this fails, when using :'want' in the test, with

ERROR: cannot open EXECUTE query as cursor

and fails with :want as follows:

ERROR: syntax error at or near "("
LINE 12: ,VALUES(1,-1,-1,1,-1)

I'm not convinced this arrangement is a good idea, but it would be useful to
know if it is solvable.

Thoughts gratefully received,
Rory

[David E. Wheeler]

On Feb 26, 2022, at 07:56, Rory Campbell-Lange <ro...@campbell-lange.net> wrote:

> \set testname "test 33: description'
> \set want VALUES(1, -1, -1, 1, -1)
>
> Trying this fails, when using :'want' in the test, with
>
> ERROR: cannot open EXECUTE query as cursor
>
> and fails with :want as follows:
>
> ERROR: syntax error at or near "("
> LINE 12: ,VALUES(1,-1,-1,1,-1)
>
> I'm not convinced this arrangement is a good idea, but it would be useful to
> know if it is solvable.

Can you post a complete example? It very much depends on what’s used where.

Best,

David

[Rory Campbell-Lange]

A complete test (excluding setup) is as follows:

\set testname 'A.01 standard'

SELECT results_eq (
$$
SELECT

ecreat, rcreat, sscreat, dcreat, dsscreat
FROM
central.fn_central(t.wk(0), t.wk(0), testing => true)
WHERE
dater = t.wk(0)
$$
,
$$

VALUES(1, -1, -1, 1, -1)

$$
,:'testname'
);

"t" is a temporary test data schema with some helper functions.

Is there a way of using :type variables to hold the "VALUES(1, -1, -1, 1, -1)"
statement?

Rory

[Rory Campbell-Lange]

On 26/02/22, Rory Campbell-Lange (ro...@campbell-lange.net) wrote:
> On 26/02/22, David E. Wheeler (da...@justatheory.com) wrote:
> > On Feb 26, 2022, at 07:56, Rory Campbell-Lange <ro...@campbell-lange.net> wrote:

> \set testname 'A.01 standard'
>
> SELECT results_eq (
> $$
> SELECT
> ecreat, rcreat, sscreat, dcreat, dsscreat
> FROM
> central.fn_central(t.wk(0), t.wk(0), testing => true)
> WHERE
> dater = t.wk(0)
> $$
> ,
> $$
> VALUES(1, -1, -1, 1, -1)
> $$
> ,:'testname'
> );
>

Ah, I can do this:

\set testname 'A.01 standard'

PREPARE want AS
SELECT
1 as ecreat
,-1 as rcreat
,-1 as sscreat
, 1 as dcreat
,-1 as dsscreat
;

SELECT results_eq (
$$
SELECT
ecreat, rcreat, sscreat, dcreat, dsscreat
FROM
central.fn_central(t.wk(0), t.wk(0), testing => true)
WHERE
dater = t.wk(0)
$$
,

'want'
,:'testname'
);

Rory

[David E. Wheeler]

On Feb 26, 2022, at 14:38, Rory Campbell-Lange <ro...@campbell-lange.net> wrote:

> Is there a way of using :type variables to hold the "VALUES(1, -1, -1, 1, -1)"
> statement?

Just include them in the first list of values, e.g.,

VALUES(1::int, -1::int, -1::int, 1::int, -1:int)

Best,

David