[SQL] security definer function
Hi I have two functions: A) function defined with "SECURITY DEFINER" B) function defined with "SECURITY INVOKER" Function A calls function B. How is the function b called - with rights of definer of function A, or rather with rights of caller of function A ? Regards, Tomasz Myrta ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] strange DEFERRABLE behaviour
Hi I've got some tables defined as: create table xx ( some_column references master_table It means this column is defined by default: NOT DEFERRABLE, INITIALLY IMMEDIATE I tried replacing rows in this table by new ones as: set autocommit=off; begin; set constraints all deferred; delete from insert ... insert... commit; I get "integrity violation"... just after "delete" If I well understood manual, it works like it should. I'm not sure, because if I run this query in pgAdmin2, it works fine (why?) and replaces rows as needed. When I drop this foreign key constraint (not too easy without a constraint name) and recreate it as "DEFERRABLE", this query works fine also in psql. My question is: Why my query works fine when using pgAdmin, and it fails when using psql? Regards, Tomasz Myrta ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Wierd postgres Problem
On Tue, Feb 13, 2001 at 10:20:09AM -0500, Najm Hashmi wrote: > Hi All, hi > I am trying to define a new set of tables an I am getting this strange > syntex problem on date, or timestamp data types. I am also getting error on > not null constranit as well... Postgres is behaving strangely first > definations with not null > > cmdb=# create table media_received ( > cmdb(# comp_id not null, > cmdb(# dept_id not null, [...] > is something wrong with my table definition syntex? Yes, you forgot to specify types for comp_id and dept_id fields. greetings, Tom -- .signature: Too many levels of symbolic links
Re: [SQL] random
On Mon, Mar 05, 2001 at 02:12:15PM -0500, Jelle Ouwerkerk wrote:
> Hi
>
> What would be the best way to select a random row from a result set?
>
> Possibilities:
>
> 1) o get the total number of rows using count()
>o generate a random number between 1 and the total
>o select the n'th row using OFFSET
>
> 2) o get the total number of rows using count()
>o generate a random number between 1 and the total
>o fetch n times
Here's my approach. It allows to get random row from the table, not from result
set in general. Maybe you will be able to modify it to fit your needs.
The table, which we want to get random row from, is not shown here. Let's
assume that it has primary key on integer type column. In the table rnd we keep
references to this table in value column.
Other columns are needed by our selection mechanism. We may want 'values' to be
duplicated (different weights), so there is this 'id' column which uniquely
identifies rnd's row.
CREATE SEQUENCE rnd_seq;
CREATE TABLE rnd (
id INT4 NOT NULL DEFAULT NEXTVAL('rnd_seq'),
r DOUBLE PRECISION NOT NULL DEFAULT random(),
value INT4,
PRIMARY KEY (id)
);
/* My idea is to index this table with random values.
* Then it is sufficent to take first row in this order to get a random one
* and of course we have to modify its r (random) fields not to get it again,
* and again, ...
*
* I hope you understand my explanation in weak english */
CREATE INDEX rnd_r_idx ON rnd (r);
/* This function does it. SELECT it issues is very fast, 'cause it uses an
* index */
CREATE FUNCTION get_rnd() RETURNS INT4 AS '
DECLARE
rowid INT4;
val INT4;
BEGIN
SELECT id, value INTO rowid, val
FROM rnd
ORDER BY r
LIMIT 1;
IF NOT FOUND THEN RETURN NULL; END IF;
UPDATE rnd SET r = random() WHERE id = rowid;
RETURN val;
END;
' LANGUAGE 'plpgsql';
/* This function only fills the table with test data */
CREATE FUNCTION fill() RETURNS INT4 AS '
DECLARE
i INT4;
BEGIN
i := ;
WHILE i <> 0 LOOP
INSERT INTO rnd (value) VALUES (i);
i := i - 1;
END LOOP;
RETURN 0;
END;
' LANGUAGE 'plpgsql';
VACUUM ANALYZE rnd;
What do you think? Is it a good idea?
greetings,
Tom
--
.signature: Too many levels of symbolic links
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] random
On Wed, Mar 07, 2001 at 12:50:36PM +0100, Tomek Zielonka wrote: > On Mon, Mar 05, 2001 at 02:12:15PM -0500, Jelle Ouwerkerk wrote: > > Hi > > > > What would be the best way to select a random row from a result set? > > > > Here's my approach. It allows to get random row from the table, not from result > set in general. Maybe you will be able to modify it to fit your needs. [cut] > What do you think? Is it a good idea? I tested it myself and unfortunately it doesn't work as should. I have to rethink it. greetings, Tom -- .signature: Too many levels of symbolic links ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
