Thread: ...

...

From

owner

Date:

19 February 2003, 06:01:14

hi, i have some question and propos.
1. how can i get count of result's rows where i declare cursor ??
 i need it to declare momeory , but i dont know how ...

2. I sometime select look like for example select 123 as "Foo 3" , 456 as
"Foo 4 ... ", and writing where is fanny.. in select should be somthing
like [] what == name of col. select would look : "select 232 as "QWE RTYU"
where [1] LIKE 'QWE%'; like in C or C++ or in any other lang.

3. Good thing will be to have somthing like suid in functions. When I do
select some_func ('arg1', 2 ,3 ) ; function should read table, and all
object like owner of this function. Most select do my functions, from many
tabels, and somtimes i have to give some person access to db and tables,
bo i want not give them all tables but only special rows, and it will be
good for just function with suid .. can you understand me ? :)
Also can be function to get user id, or name, and ip etc ...

4. Is possible to bring pg_hba.conf to db , in to pg_* tables ??

Maybe some of this tinks has been writen already but i dont know about it
, or maybe i should write it self ?


if someone can help me and tell me something about this thinks , please
mail to me ( ivan@psycho.pl )

thx, sorry for my english :)
 bye, ivan

Re:

From

"Nigel J. Andrews"

Date:

19 February 2003, 07:07:15

On Wed, 19 Feb 2003, owner wrote:

>
> hi, i have some question and propos.
> 1. how can i get count of result's rows where i declare cursor ??
>  i need it to declare momeory , but i dont know how ...

Perhaps you don't need to use a cursor if you are allocating storage for all
the result set rather than processing small batches of rows.

>
> 2. I sometime select look like for example select 123 as "Foo 3" , 456 as
> "Foo 4 ... ", and writing where is fanny.. in select should be somthing
> like [] what == name of col. select would look : "select 232 as "QWE RTYU"
> where [1] LIKE 'QWE%'; like in C or C++ or in any other lang.

I don't understand what you are asking here. I am not sure others will either.
Could you post an example in C so we can see what it is you mean?

> 3. Good thing will be to have somthing like suid in functions. When I do
> select some_func ('arg1', 2 ,3 ) ; function should read table, and all
> object like owner of this function. Most select do my functions, from many
> tabels, and somtimes i have to give some person access to db and tables,
> bo i want not give them all tables but only special rows, and it will be
> good for just function with suid .. can you understand me ? :)
> Also can be function to get user id, or name, and ip etc ...

You can use views to achieve some of this. I think the following would work.

CREATE TABLE mytable ( a integer );
CREATE VIEW myview AS SELECT * FROM mytable WHERE a = 24;
GRANT SELECT ON myview TO otheruser;

>
> 4. Is possible to bring pg_hba.conf to db , in to pg_* tables ??

There is no pg_ table for this. You could process the file and store the data
in a table but it would only be useful for reporting the contents of the table
and not necessarily reflect the file contents.

--
Nigel J. Andrews

Re: Four questions

From

greg@turnstep.com

Date:

19 February 2003, 13:01:29

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


> 1. how can i get count of result's rows where i declare cursor ??

Nothing in DECLARE will tell you this: you will have to do a
SELECT COUNT(*) using the same WHERE clause as the DECLARE to figure
this out. You can always set aside a set amount of memory and only FETCH
that many rows each time of course:

FETCH 23 FROM mycursor;

> 2. I sometime select look like for example select 123 as "Foo 3" , 456 as
> "Foo 4 ... ", and writing where is fanny.. in select should be somthing
> like [] what == name of col. select would look : "select 232 as "QWE RTYU"
> where [1] LIKE 'QWE%'; like in C or C++ or in any other lang.

You can use aliases in the WHERE clause, but you must be careful about it.

Not sure what you are asking here, maybe you could ask again with a
different example?


> 3. Good thing will be to have somthing like suid in functions. When I do
> select some_func ('arg1', 2 ,3 ) ; function should read table, and all
> object like owner of this function.

Functions are run with the same privileges as the user that calls it by default.
You can change this by adding "SECURITY DEFINER" when creating the function:

http://www.gtsm.com:81/cgi/psearch?page=sql-createfunction.html;q=security;j=on;c=0;h=1


4. Is possible to bring pg_hba.conf to db, in to pg_* tables ??

I'm sure someone has done this, but I don't have a pointer handy.


- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200302190932
-----BEGIN PGP SIGNATURE-----
Comment: http://www.turnstep.com/pgp.html

iD8DBQE+U5rrvJuQZxSWSsgRAs5AAJ4vRL4NCOBDvaGbj9n/gzhoml8hoQCguc8N
Nua9LrCzWXwEkDXDcuPsoAw=
=YMny
-----END PGP SIGNATURE-----

Re: Four questions

From

owner

Date:

19 February 2003, 19:08:31


On Wed, 19 Feb 2003 greg@turnstep.com wrote:

>
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
>
> > 1. how can i get count of result's rows where i declare cursor ??
>
> Nothing in DECLARE will tell you this: you will have to do a
> SELECT COUNT(*) using the same WHERE clause as the DECLARE to figure
> this out. You can always set aside a set amount of memory and only FETCH
> that many rows each time of course:
>
> FETCH 23 FROM mycursor;
>

    hmm, this select its very big select, including many tables and
throught func. Making this same query 2 times is without any sens,
pointless. I make this select trought libpq, from server to others PC, and
every Exec need same time, and befor dowloading rows to client memory i
need to know exacly how big is select .... :/

> > 2. I sometime select look like for example select 123 as "Foo 3" , 456 as
> > "Foo 4 ... ", and writing where is fanny.. in select should be somthing
> > like [] what == name of col. select would look : "select 232 as "QWE RTYU"
> > where [1] LIKE 'QWE%'; like in C or C++ or in any other lang.
>
> You can use aliases in the WHERE clause, but you must be careful about it.
>
> Not sure what you are asking here, maybe you could ask again with a
> different example?
>
    For example i do select and i dont know name of the cols, but i
know type, and count of cos, and i want to do what i can do with ORDER
 : select * from func() where [1] != NULL order by 1;
[1] == name of first col in this select

>
> > 3. Good thing will be to have somthing like suid in functions. When I do
> > select some_func ('arg1', 2 ,3 ) ; function should read table, and all
> > object like owner of this function.
>
> Functions are run with the same privileges as the user that calls it by default.
> You can change this by adding "SECURITY DEFINER" when creating the function:
>
> http://www.gtsm.com:81/cgi/psearch?page=sql-createfunction.html;q=security;j=on;c=0;h=1
>
    then i can use same func like other user ?
>
> 4. Is possible to bring pg_hba.conf to db, in to pg_* tables ??
>
> I'm sure someone has done this, but I don't have a pointer handy.
>
>
> - --
> Greg Sabino Mullane greg@turnstep.com
> PGP Key: 0x14964AC8 200302190932
> -----BEGIN PGP SIGNATURE-----
> Comment: http://www.turnstep.com/pgp.html
>
> iD8DBQE+U5rrvJuQZxSWSsgRAs5AAJ4vRL4NCOBDvaGbj9n/gzhoml8hoQCguc8N
> Nua9LrCzWXwEkDXDcuPsoAw=
> =YMny
> -----END PGP SIGNATURE-----
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>

Re: Four questions

From

greg@turnstep.com

Date:

20 February 2003, 14:26:20

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


>                                 hmm, this select its very big select, including many tables and
> throught func. Making this same query 2 times is without any sens,
> pointless. I make this select trought libpq, from server to others PC, and
> every Exec need same time, and befor dowloading rows to client memory i
> need to know exacly how big is select .... :/

There is no way to know how many rows a query is until you actually run the
query. A DECLARE statement does not actually run the query. Why not set aside
memory for a specific amount of rows and FETCH with a number as suggested?


>                                For example i do select and i dont know name of the cols, but i
> know type, and count of cos, and i want to do what i can do with ORDER
> : select * from func() where [1] != NULL order by 1;
> [1] == name of first col in this selec


Sorry, you cannot do it like that, you must specify the actual column, not the
alias, inside of the WHERE clause. If your column is an expression, you will
have to repeat that expression in the WHERE clause as well:

SELECT a+b as "abba", c, d FROM mytable WHERE c=12 AND a+b < 10 ORDER BY 1;

or..

SELECT a+b as "abba", c, d FROM mytable WHERE c=12 AND a+b < 10 ORDER BY a+b;

or..

SELECT a+b as "abba", c, d FROM mytable WHERE c=12 AND a+b < 10 ORDER BY "abba";

I prefer the last one, as the alias name in the ORDER BY is clearer than the
number and less likely to cause future problems when you change "abba" inside
of the select.


> ...then i can use same func like other user ?

Yes. :)

- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200302201119

-----BEGIN PGP SIGNATURE-----
Comment: http://www.turnstep.com/pgp.html

iD8DBQE+VQAjvJuQZxSWSsgRAiZ4AJ4lfk4mM3b72r79ydRHKH9qzr/oiwCdE1hw
kxErBUEoquUVG/3lzUcjqKs=
=1d5S
-----END PGP SIGNATURE-----