Thread: Postgresql 9.5 and Shell scripts/variables vs. C programming/defining a value to be used

On Mon, Nov 21, 2016 at 11:22 AM, Poul Kristensen <bcc5226@gmail.com> wrote:

Thank you for fast repons!

The $1 substitution below. I assume that it refers to "joe's place". But it is not very clear to me, how "joe's place" will appear instead of $1 when running. Where is it possiible to read more about this? There just is'nt much about substitution in C online. Any recommended books to buy?  

/* Here is our out-of-line parameter value */

    paramValues[0] = "joe's place";

    res = PQexecParams(conn,

                       "SELECT * FROM test1 WHERE t = $1",

                       1,       /* one param */

                       NULL,    /* let the backend deduce param type */

                       paramValues,

                       NULL,    /* don't need param lengths since text */

                       NULL,    /* default to all text params */

                       1);      /* ask for binary results */

}

/Poul

​It is described better here: https://www.postgresql.org/docs/9.6/static/libpq-exec.html

than I can do. But I just noticed a mistake in your code, or maybe just something left out. I would say:

char *value1 = "joe's place";

​​

char **paramV

​a​

lues = &value1;

​ /* closer match to the documentation's syntax */​

//char *paramValues[] = {"joe's place"}; /* same as above, different syntax */

//

//char *paramValues[1]; /* this looks to be missing */

//paramValues[0]="joe's place"; /* what you had */

res = PQexecParms(conn,

           "SELECT * FROM test1 WHERE t = $1",

           1, /* there is only 1 entry in paramValues array */

           paramValues, /* address of parameter value array */

           NULL, /* don't need param lengths since text */

           NULL, /* defaul to all text params */

           1); /* return all values as binary */

Well, you have an array of pointers to characters called paramValues. The $1 refers to whatever is pointed to by paramValues[0]​, which is a pointer to value1 which is a C "string". Basically in the second parameter, the command, the $n is used as a 1-based index into the paramValues[] array. This means that the actual C language array value is one less (since C arrays are 0-based). Which means that "$n" (n>=1) in the "command" string refers to value pointed to by paramValues[n-1]. The 3rd value, 1 in this case, tells PQexecParms how many entries there are in the paramValues[] array. I guess this is a type of validity check that the $n in the command string is not too large for the array.

Note: please keep the discussion on the list, not to me personally. It may be of help to others (or maybe not, I don't know.)

--

Heisenberg may have been here.

Unicode: http://xkcd.com/1726/

Maranatha! <><
John McKown

On Tue, Nov 22, 2016 at 8:22 AM, Poul Kristensen <bcc5226@gmail.com> wrote:

I think I understand. 

When I use this in my code I get 

"undefined reference to `PQexecParms' 

​The correct name is PQexecParams (note the last "a"). Sorry I missed that when first looking.​ Also, just to be sure, did you include the argument "-lpq" on the compile command to point to the PostgreSQL library for linking?

 

when compiling.

references in main is

const char *conninfo; /* connection string  to the database */ 

PGconn *conn; /* connection to the database */

PGresult *res; /* result of sql query */ 

int   nFields;  /* print out the attribute names */

int i; / * print the columns */

     j;

Is the a reserved reference to use with

Reserved res = PQexecParms(conn.... )

Then I assume that I have to use another reference than res.

Thanks.

/Poul 

--

Heisenberg may have been here.

Unicode: http://xkcd.com/1726/

Maranatha! <><
John McKown

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Wed, Nov 23, 2016 at 3:46 AM, Poul Kristensen <bcc5226@gmail.com> wrote:
>> In the postgresql.log I get "select datname from $1".

> ​That is expected when you parameterize a query - the query string includes
> parameter​s instead of values.

It looks like Poul is hoping to use a parameter as a table name,
which doesn't work.  Parameters can only represent values (constants).

                        regards, tom lane

My goal is to grap a user from the OS and and substitute the grapped user - to be the owner of the database - in the sql commands

e.g using shell-script substitution syntax: 

       create user ${user} with login; 

       create tablespace ${user} location '/path/${user)'; 

       create database ${user} owner ${user} on tablespace ${user};

I would be surpriced if this should not be possible using gnu gcc but I might be wrong. It is just a question of figurering out way to do this. 

http://zetcode.com/db/postgresqlc/ has an example of how to do this using integer as argument on 

the commandline.

Do you have a hint of the substitution syntax to be used in gnu gcc.

Thanks.

/Poul

2016-11-23 16:44 GMT+01:00 Tom Lane <tgl@sss.pgh.pa.us>:

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Wed, Nov 23, 2016 at 3:46 AM, Poul Kristensen <bcc5226@gmail.com> wrote:
>> In the postgresql.log I get "select datname from $1".

> ​That is expected when you parameterize a query - the query string includes
> parameter​s instead of values.

It looks like Poul is hoping to use a parameter as a table name,
which doesn't work.  Parameters can only represent values (constants).

                        regards, tom lane

--

Med venlig hilsen / Best regards
Poul Kristensen
Linux-OS/Virtualizationexpert and Oracle DBA