tdbc::postgres

Check-in [9485f6bb80]
Login
Bounty program for improvements to Tcl and certain Tcl packages.
Tcl 2019 Conference, Houston/TX, US, Nov 4-8
Send your abstracts to [email protected]
or submit via the online form by Sep 9.

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Support specifying the schema name when querying tables or columns
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | medranocalvo-correct-quoting
Files: files | file ages | folders
SHA3-256: 9485f6bb802a28389c8e779b42428c87f1360a99415031b64d2d0df480a42953
User & Date: adrianmedranocalvo 2018-09-14 18:58:22
References
2018-09-14
19:08 Ticket [ed1c750d97] tdbc columns method tries to select all data from a table status still Open with 6 other changes artifact: 7f42e81f2e user: adrianmedranocalvo
19:07 Ticket [2e14854f4f] db columns table doesn't recognize schema names status still Open with 7 other changes artifact: 5097cd7482 user: adrianmedranocalvo
Context
2018-09-14
18:58
Support specifying the schema name when querying tables or columns Leaf check-in: 9485f6bb80 user: adrianmedranocalvo tags: medranocalvo-correct-quoting
18:10
Add copyright information check-in: e1181bd5af user: adrianmedranocalvo tags: medranocalvo-correct-quoting
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to generic/tdbcpostgres.c.

1472
1473
1474
1475
1476
1477
1478


1479
1480
1481
1482
1483
1484






1485
1486
1487
1488
1489
1490
1491
1492


1493







1494

1495
1496
1497
1498
1499
1500
1501
....
1505
1506
1507
1508
1509
1510
1511
1512



1513
1514







1515

1516
1517
1518
1519
1520
1521
1522
....
1739
1740
1741
1742
1743
1744
1745


1746


1747
1748
1749
1750
1751
1752
1753

















1754
1755
1756
1757
1758
1759
1760
1761


1762


1763
1764
1765

1766
1767
1768
1769
1770
1771
1772
1773
1774
				/* Literal pool */
    PGresult* res,* resType;	/* Results of libpq call */
    char* columnName;		/* Name of the column */
    Oid typeOid;		/* Oid of column type */
    Tcl_Obj* retval;		/* List of table names */
    Tcl_Obj* attrs;		/* Attributes of the column */
    Tcl_Obj* name;		/* Name of a column */


    Tcl_DString ds, *dsPtr = &ds;

    /* Check parameters */

    if (objc < 3 || objc > 4) {
	Tcl_WrongNumArgs(interp, 2, objv, "table ?pattern?");






	return TCL_ERROR;
    }

    Tcl_DStringInit(dsPtr);

    /* Check if table exists by retreiving one row.
     * The result wille be later used to determine column types (oids) */
    Tcl_DStringAppend(dsPtr, "SELECT * FROM ", -1);


    AppendSQLIdentifier(dsPtr, objv[2]);







    Tcl_DStringAppend(dsPtr, " LIMIT 0", -1);


    if (ExecSimpleQuery(interp, cdata->pgPtr, Tcl_DStringValue(dsPtr),
			&resType) != TCL_OK) {
	Tcl_DStringFree(dsPtr);
	return TCL_ERROR;
    }

................................................................................

    Tcl_DStringAppend(dsPtr, "SELECT "
	"  column_name,"
	"  numeric_precision,"
	"  character_maximum_length,"
	"  numeric_scale,"
	"  is_nullable"
	"  FROM information_schema.columns"



	"  WHERE table_name=", -1);
    AppendSQLStringConstant(dsPtr, objv[2]);









    if (objc == 4) {
	Tcl_DStringAppend(dsPtr, " AND column_name LIKE ", -1);
	AppendSQLStringConstant(dsPtr, objv[3]);
    }

    if (ExecSimpleQuery(interp, cdata->pgPtr,
			Tcl_DStringValue(dsPtr), &res) != TCL_OK) {
................................................................................
				/* Instance data */
    Tcl_Obj** literals = cdata->pidata->literals;
				/* Literal pool */
    PGresult* res;		/* Result of libpq call */
    char * field;		/* Field value from SQL result */
    Tcl_Obj* retval;		/* List of table names */
    int i;


    Tcl_DString ds, *dsPtr = &ds;



    /* Check parameters */

    if (objc < 2 || objc > 3) {
	Tcl_WrongNumArgs(interp, 2, objv, "");
	return TCL_ERROR;
    }


















    Tcl_DStringInit(dsPtr);
    Tcl_DStringAppend(dsPtr,
		      "SELECT tablename"
		      " FROM pg_tables"
		      " WHERE  schemaname = 'public'",
		      -1); /* SQL query for table list */



    if (objc == 3) {



	/* Pattern string is given */


	Tcl_DStringAppend(dsPtr, " AND  tablename LIKE ", -1);
	AppendSQLStringConstant(dsPtr, objv[2]);
    }

    /* Retrieve the table list */

    if (ExecSimpleQuery(interp, cdata ->pgPtr, Tcl_DStringValue(dsPtr),
			&res) != TCL_OK) {
	Tcl_DStringFree(dsPtr);






>
>





|
>
>
>
>
>
>






|

>
>
|
>
>
>
>
>
>
>

>







 







|
>
>
>
|
|
>
>
>
>
>
>
>
|
>







 







>
>

>
>




|


>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>



|
<
<


>
>
|
>
>
|
<

>
|
|







1472
1473
1474
1475
1476
1477
1478
1479
1480
1481
1482
1483
1484
1485
1486
1487
1488
1489
1490
1491
1492
1493
1494
1495
1496
1497
1498
1499
1500
1501
1502
1503
1504
1505
1506
1507
1508
1509
1510
1511
1512
1513
1514
1515
1516
1517
1518
1519
....
1523
1524
1525
1526
1527
1528
1529
1530
1531
1532
1533
1534
1535
1536
1537
1538
1539
1540
1541
1542
1543
1544
1545
1546
1547
1548
1549
1550
1551
....
1768
1769
1770
1771
1772
1773
1774
1775
1776
1777
1778
1779
1780
1781
1782
1783
1784
1785
1786
1787
1788
1789
1790
1791
1792
1793
1794
1795
1796
1797
1798
1799
1800
1801
1802
1803
1804
1805
1806
1807


1808
1809
1810
1811
1812
1813
1814
1815

1816
1817
1818
1819
1820
1821
1822
1823
1824
1825
1826
				/* Literal pool */
    PGresult* res,* resType;	/* Results of libpq call */
    char* columnName;		/* Name of the column */
    Oid typeOid;		/* Oid of column type */
    Tcl_Obj* retval;		/* List of table names */
    Tcl_Obj* attrs;		/* Attributes of the column */
    Tcl_Obj* name;		/* Name of a column */
    Tcl_Obj** namev;		/* Table schema (optional) and table name. */
    int namec;
    Tcl_DString ds, *dsPtr = &ds;

    /* Check parameters */

    if (objc < 3 || objc > 4) {
	Tcl_WrongNumArgs(interp, 2, objv, "{?schema? table} ?pattern?");
	return TCL_ERROR;
    }

    if (Tcl_ListObjGetElements(interp, objv[2], &namec, &namev) != TCL_OK
	|| namec < 1 || namec > 2) {
	Tcl_WrongNumArgs(interp, 2, objv, "{?schema? table} ?pattern?");
	return TCL_ERROR;
    }

    Tcl_DStringInit(dsPtr);

    /* Check if table exists by retreiving one row.
     * The result will be later used to determine column types (oids) */
    Tcl_DStringAppend(dsPtr, "SELECT * FROM ", -1);
    switch (namec) {
    case 1:                  /* table_name */
	AppendSQLIdentifier(dsPtr, namev[0]);
	break;
    case 2:                  /* table_schema.table_name */
	AppendSQLIdentifier(dsPtr, namev[0]);
	Tcl_DStringAppend(dsPtr, ".", 1);
	AppendSQLIdentifier(dsPtr, namev[1]);
	break;
    }
    Tcl_DStringAppend(dsPtr, " LIMIT 0", -1);


    if (ExecSimpleQuery(interp, cdata->pgPtr, Tcl_DStringValue(dsPtr),
			&resType) != TCL_OK) {
	Tcl_DStringFree(dsPtr);
	return TCL_ERROR;
    }

................................................................................

    Tcl_DStringAppend(dsPtr, "SELECT "
	"  column_name,"
	"  numeric_precision,"
	"  character_maximum_length,"
	"  numeric_scale,"
	"  is_nullable"
	"  FROM information_schema.columns", -1);

    switch (namec) {
    case 1:
	Tcl_DStringAppend(dsPtr, " WHERE table_name=", -1);
	AppendSQLStringConstant(dsPtr, namev[0]);
	break;
    case 2:
	Tcl_DStringAppend(dsPtr, " WHERE table_schema=", -1);
	AppendSQLStringConstant(dsPtr, namev[0]);
	Tcl_DStringAppend(dsPtr, " AND table_name=", -1);
	AppendSQLStringConstant(dsPtr, namev[1]);
	break;
    }
 
    if (objc == 4) {
	Tcl_DStringAppend(dsPtr, " AND column_name LIKE ", -1);
	AppendSQLStringConstant(dsPtr, objv[3]);
    }

    if (ExecSimpleQuery(interp, cdata->pgPtr,
			Tcl_DStringValue(dsPtr), &res) != TCL_OK) {
................................................................................
				/* Instance data */
    Tcl_Obj** literals = cdata->pidata->literals;
				/* Literal pool */
    PGresult* res;		/* Result of libpq call */
    char * field;		/* Field value from SQL result */
    Tcl_Obj* retval;		/* List of table names */
    int i;
    Tcl_Obj** namev;		/* Optional schema name and table name pattern */
    int namec;
    Tcl_DString ds, *dsPtr = &ds;
    Tcl_Obj *schemanameObj = NULL;
    Tcl_Obj *tablepatternObj = NULL;

    /* Check parameters */

    if (objc < 2 || objc > 3) {
	Tcl_WrongNumArgs(interp, 2, objv, "?{?schemaname? pattern}?");
	return TCL_ERROR;
    }

    if (objc == 3) {
	if (Tcl_ListObjGetElements(interp, objv[2], &namec, &namev) != TCL_OK
	    || namec < 1 || namec > 2) {
	    Tcl_WrongNumArgs(interp, 2, objv, "?{?schemaname? pattern}?");
	    return TCL_ERROR;
	}
	switch (namec) {
	case 1:
	    tablepatternObj = namev[0];
	    break;
	case 2:
	    schemanameObj = namev[0];
	    tablepatternObj = namev[1];
	    break;
	}
    }

    Tcl_DStringInit(dsPtr);
    Tcl_DStringAppend(dsPtr,
		      "SELECT tablename FROM pg_tables",


		      -1); /* SQL query for table list */

    if (schemanameObj) {
	Tcl_DStringAppend(dsPtr, " WHERE schemaname=", -1);
	AppendSQLStringConstant(dsPtr, schemanameObj);
    } else {
	Tcl_DStringAppend(dsPtr, " WHERE schemaname='public'", -1);
    }


    if (tablepatternObj) {
	Tcl_DStringAppend(dsPtr, " AND tablename LIKE ", -1);
	AppendSQLStringConstant(dsPtr, tablepatternObj);
    }

    /* Retrieve the table list */

    if (ExecSimpleQuery(interp, cdata ->pgPtr, Tcl_DStringValue(dsPtr),
			&res) != TCL_OK) {
	Tcl_DStringFree(dsPtr);