logo
down
shadow

How to test PROCEDURE in PostgreSQL with pgTAP?


How to test PROCEDURE in PostgreSQL with pgTAP?

Content Index :

How to test PROCEDURE in PostgreSQL with pgTAP?
Tag : postgresql , By : dlouzan
Date : November 28 2020, 04:01 AM

seems to work fine Here is an approach I came up with inspired by using interfaces along with mocking frameworks in other languages.
First we move the COMMIT operation to a stored procedure like this:
CREATE PROCEDURE foo.do_commit()
AS
$$
BEGIN
  COMMIT;
END;
$$
LANGUAGE plpgsql;
CREATE OR REPLACE PROCEDURE foo.do_something(IN i_value INT)
AS
$$
BEGIN
  PERFORM foo.call_function_1(i_value);
  CALL foo.do_commit();
  CALL foo.another_procedure(i_value);
END;
$$
LANGUAGE plpgsql;
CREATE FUNCTION test.test_do_something()
RETURNS SETOF TEXT 
AS 
$$
BEGIN
  CREATE TEMPORARY TABLE commit_calls
  (
    commit_call BOOLEAN NOT NULL DEFAULT TRUE
  )
  ON COMMIT DROP;

  CREATE TEMPORARY TABLE function_calls
  (
    the_value INT NOT NULL
  )
  ON COMMIT DROP;

  CREATE OR REPLACE PROCEDURE foo.do_commit()
  AS
  $mock_do_commit$
  BEGIN
    INSERT INTO commit_calls (commit_call)
    VALUES (DEFAULT);
  END;
  $mock_do_commit$
  LANGUAGE plpgsql;

  CREATE OR REPLACE FUNCTION foo.call_function_1(i_value INT)
  RETURNS VOID
  AS
  $mock_call_function_1$
    INSERT INTO function_calls (the_value)
    VALUES (i_value);
  $mock_call_function_1$
  LANGUAGE sql;

  -- EXECUTE
  CALL foo.do_something(9);
  CALL foo.do_something(100);

  -- VERIFY
  RETURN NEXT assert.is((SELECT COUNT(*) FROM commit_calls)::INT, 2, 'verify transaction commits');
  RETURN NEXT assert.bag_eq(
    'SELECT the_value FROM function_calls',
    'VALUES (9), (100)',
    'verify function call values');
END;
$$
LANGUAGE plpgsql;

Comments
No Comments Right Now !

Boards Message :
You Must Login Or Sign Up to Add Your Comments .

Share : facebook icon twitter icon

pgtap: expected and obtained result are equal, yet the test fails


Tag : postgresql , By : SA.
Date : March 29 2020, 07:55 AM
help you fix your problem You don't give all the details, but I suspect it's a data type mismatch. pgTAP tends to require exact matches. Try this:
PREPARE result_want AS VALUES ('("2010-09-07 06:05:00+00",100.0)'::foo);

How do I write tests in Perl with pgTAP?


Tag : perl , By : msg
Date : March 29 2020, 07:55 AM
wish of those help pgTap is for parsing SQL output - output from running an SQL script via psql from the command line. An SQL script that contains pgTap SQL calls mixed in with the SQL statements that you wish to test.
As such, DBI doesn't enter into it.

Procedure to get the parameters and result type of a PostgreSQL stored procedure?


Tag : database , By : CodeOfficer
Date : March 29 2020, 07:55 AM
This might help you What is the procedure to get the parameter and return types of a stored procedure? (Google is of no help :-( ). , Here is one way:
SELECT pg_get_function_result(oid), pg_get_function_arguments(oid)
FROM pg_proc
WHERE proname = 'GetAllDx'
SELECT oid::regprocedure FROM pg_proc WHERE proname = 'GetAllDx'

Is it a good way to use pgTAP framework to test postgresql functions?


Tag : ruby-on-rails , By : msg
Date : March 29 2020, 07:55 AM

How to install pgTAP on Windows?


Tag : unit-testing , By : tommy
Date : March 29 2020, 07:55 AM
Related Posts Related QUESTIONS :
  • Connect between client docker and postgres server on host
  • pgadmin can't log in after update
  • what is the postgresql query to find the timestamp without time zone
  • Port-foward command to access postgres from outside AKS not working in kubectl due to ampersand
  • Combine rows in postgres
  • How to find data length and index length of particular tables in a postgresql schema?
  • Shell Script To Write PostgreSQL SQL Query Results In To CSV Format
  • DOCKER environment in production
  • TimescaleDB query to select rows where column value changed from previous row
  • Temporary Postgres table gets lost prematurely
  • Partial unique index in postgres in relation to other rows
  • API returns only last row for a nested struct
  • Converting PostGIS table (postgresql) to GeoJSON
  • How to find if the row is part of index?
  • pgAdmin argument formats can't be mixed
  • How to calculate subtotal in postgreSQL
  • Restoring PostgreSQL database without having a dump just the database files
  • Best way to track the progress of a long-running function (from outside) - PostgreSQL 11?
  • Add columns but keep a specific id
  • pg_restore: [archiver] unsupported version (1.13) in file header
  • How to DB query and then check the result
  • Select multiple rows with condition on each columns
  • Postgres date format with Greek day inside
  • Firing Postgres triggers on different table columns
  • Prepared transactions with Postgres 8.4.3 on CentOS
  • OpenStreetMap and Hadoop
  • OSGI & Apache Commons-DBCP Classloading Issue
  • Postgresql full text search in postgresql - japanese, chinese, arabic
  • Strange postgresql behavior
  • PostgreSql XML Text search
  • Can't install psycopg2 (Python2.6 Ubuntu9.10 PostgreSQL8.4.2)
  • Enable encryption on existing database - AWS RDS Postgresql
  • Tagging DB with formatted SQL + XML
  • Query from a variable table in postgres
  • Returning record ID's from PostgreSQL using PetaPoco
  • Why am I getting an error that I cannot concat two different datatypes even after casting the fields datatype
  • Writing a rather obtuse JSON query using Slick
  • How to set a dynamic "where" in a view based on the user querying data?
  • K8S use volume to keep DB data
  • Merging an array of JSON objects to one JSON column in Postgres
  • Update values of a row to the max of old and new value
  • I want to get the book that has been issued the maximum no. of times
  • Pg-promise client doesn't retrieve special characters properly from ElephantSQL cloud database
  • pg_dump/pg_restore lc_collate English_United States.1252 Windows Linux migration
  • Set default value from a query result
  • Function not found in PostgreSQL
  • How to get postgresql_query results from Ansible
  • pg_dump to copy schema to remote server
  • PostgreSQL - export output to CSV
  • How to sum/total counts by group?
  • Postgres Check availabilty overlapping dates
  • psql copy from csv automatically generating ids
  • execute a postgresql query from a file and write the output to another csv file
  • Invoke Stored Procedure and Return ID
  • Converting PostgreSQL interval to seconds produces wrong values
  • Aggregating columns and getting count of values as row
  • Find overlapping date ranges from two tables?
  • Encoding UTF-8 problem while JPA/hibernate execute sql-script with Intelli-J
  • In PostgreSQL, which types can be cast with the type name first?
  • What is the relation between the size of the DB and the size of /var/lib/postgresql/data
  • shadow
    Privacy Policy - Terms - Contact Us © scrbit.com