[SQL] How to pass array of values to a stored procedure

[Curtis Scheer]

Does anyone have any examples of how I would make a stored
procedure in plpgsql that would allow for passing a list or arrays of values to
be used in an sql IN clause?  Like so: select * from table
where field1 in (values).

 

Thanks,

 

Curtis

Re: [SQL] hi let me know the solution to this question

[Aaron Bono]

On 7/18/06, Michael Fuhr <[EMAIL PROTECTED]> wrote:
On Tue, Jul 18, 2006 at 10:46:29AM +0530, Penchalaiah P. wrote:> Can u let me know if at all any system defined variable exists to check> whether insert , update , and delete is successfully executed in a
> stored procedure>> So that I can perform some steps based on the  result of execution if> possible please send me the sample code...Depending on what you mean by "successfully executed," see "Obtaining
the Result Status" or "Trapping Errors" in the PL/pgSQL documentation(error trapping is available in 8.0 and later).
http://www.postgresql.org/docs/8.1/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-DIAGNOSTICS
http://www.postgresql.org/docs/8.1/interactive/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING OK, this question got me wondering: is there a way to determine, in a function/stored procedure, the number of rows that were inserted/updated/deleted?
For example, the following does NOT work but shows what I am trying to achieve:CREATE OR REPLACE FUNCTION "public"."test_fn" () RETURNS VOID AS'DECLARE    mycount INTEGER;
BEGIN    -- This assignment will not work be I want something like it    -- so I can count the number of records updated.    mycount := EXECUTE        ''update mytable '' ||        ''mycolumn = 1 '' ||
        ''WHERE '' ||        ''    mycolumn = 2 ''    ;        RAISE NOTICE ''count = %'', mycount;        RETURN;END;'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
I could not find any documentation about getting the count of updates but when you run the update by itself, PostgreSQL reports the number of records updated so I have to believe the information is available somewhere.
Thanks,Aaron==   Aaron Bono   Aranya Software Technologies, Inc.   http://www.aranya.com
==

Re: [SQL] hi let me know the solution to this question

[Bricklen Anderson]

Aaron Bono wrote:

On 7/18/06, *Michael Fuhr* <[EMAIL PROTECTED] > wrote:




http://www.postgresql.org/docs/8.1/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-DIAGNOSTICS


 
OK, this question got me wondering: is there a way to determine, in a 
function/stored procedure, the number of rows that were 
inserted/updated/deleted?


I could not find any documentation about getting the count of updates 
but when you run the update by itself, PostgreSQL reports the number of 
records updated so I have to believe the information is available 
somewhere.


Thanks,
Aaron

Look for the section entitled "36.6.6. Obtaining the Result Status" on 
the link that Michael Fuhr supplied (above). Is that what you are 
looking for?


---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [SQL] How to pass array of values to a stored procedure

[Tom Lane]

Curtis Scheer <[EMAIL PROTECTED]> writes:
> Does anyone have any examples of how I would make a stored procedure in
> plpgsql that would allow for passing a list or arrays of values to be used
> in an sql IN clause?  Like so: select * from table where field1 in (values).

Probably the "field1 = ANY (arrayvalue)" syntax is what you want.  Note
however that this can't be turned into an indexscan on field1 in existing
releases (8.2 will be able to do it).

regards, tom lane

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [SQL] How to pass array of values to a stored procedure

[Tony Wasson]

On 7/18/06, Curtis Scheer <[EMAIL PROTECTED]> wrote:

Does anyone have any examples of how I would make a stored procedure in
plpgsql that would allow for passing a list or arrays of values to be used
in an sql IN clause?  Like so: select * from table where field1 in (values).



Here's a very simple example. However, I think passing arrays of INTs
around is dirty. I wasn't able to do this without a FOR ...IN EXECUTE
statement.

CREATE TABLE ids
(
   id  INTEGER
   , PRIMARY KEY (id)
);

INSERT INTO ids VALUES (1);
INSERT INTO ids VALUES (2);
INSERT INTO ids VALUES (3);

CREATE OR REPLACE FUNCTION example_array_input(INT[]) RETURNS SETOF ids AS
$BODY$
DECLARE
   in_clause ALIAS FOR $1;
   clause  TEXT;
   rec RECORD;
BEGIN
   -- conver the array to a text string and make it LOOK like an
IN statement
   clause := in_clause;
   clause := trim(leading '{' FROM clause);
   clause := trim(trailing '}' FROM clause);

   FOR rec IN EXECUTE 'SELECT id FROM ids WHERE id IN (' || clause || ');'
   LOOP
   RETURN NEXT rec;
   END LOOP;
   -- final return
   RETURN;
END
$BODY$ language plpgsql;

SELECT * FROM example_array_input('{1,2,4,5,6}'::INT[]);

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [SQL] How to pass array of values to a stored procedure

[Tony Wasson]

On 7/18/06, Tony Wasson <[EMAIL PROTECTED]> wrote:

On 7/18/06, Curtis Scheer <[EMAIL PROTECTED]> wrote:
> Does anyone have any examples of how I would make a stored procedure in
> plpgsql that would allow for passing a list or arrays of values to be used
> in an sql IN clause?  Like so: select * from table where field1 in (values).
>


Ahhh... Here's an example using Tom's recommended field=ANY (arrayvalue) SQL.


CREATE TABLE ids
(
   id  INTEGER
   , PRIMARY KEY (id)
);

INSERT INTO ids VALUES (1);
INSERT INTO ids VALUES (2);
INSERT INTO ids VALUES (3);

CREATE OR REPLACE FUNCTION example_array_input(INT[]) RETURNS SETOF ids AS
$BODY$
DECLARE
   in_clause ALIAS FOR $1;
   clause  TEXT;
   rec RECORD;
BEGIN
   FOR rec IN SELECT id FROM ids WHERE id = ANY(in_clause)
   LOOP
   RETURN NEXT rec;
   END LOOP;
   -- final return
   RETURN;
END
$BODY$ language plpgsql;

SELECT * FROM example_array_input('{1,2,4,5,6}'::INT[]);

---(end of broadcast)---
TIP 6: explain analyze is your friend

[SQL] using constraint based paritioning to fix EAV type schemas? (view inheritance and constraints)

[Andrew Hammond]

I have a client with the following EAV inspired schema.

CREATE TABLE many_tables (
table_id text primary key,-- defines which virtual table is
encoded
attribute1 text,
attribute2 text,
attribute3 text,
attribute4 text,
...
);

I'd like to use a mix of constraint based paritioning, rules
_and_views_ to implement a real schema underneath this mess, like the
following.

CREATE TABLE cat (
 cat_id INTEGER PRIMARY KEY,
 cat_name TEXT NOT NULL,
 aloofness NUMERIC(1,3) DEFAULT 1.0 CHECK (0.0 <= aloofness AND
aloofness <= 1.0)
);

CREATE RULE many_tables_cat_insert AS
ON INSERT TO many_tables WHERE table_id = 'cat' DO INSTEAD
INSERT INTO cat (cat_id, cat_name, aloofness) VALUES (
 CAST(attribute1 AS integer),
 attribute2,
 CAST(attribute3 AS numeric(1,3))
 -- gleefully ignore the other attributes
);

-- etc for UPDATE, and DELETE rules

-- This part doesn't work
CREATE VIEW many_tables_a (CHECK (table_id = 'cat')) INHERITS
(many_tables) AS
SELECT 'cat' AS table_id,
CAST(cat_id AS text) AS attribute1,
cat_name AS attribute2,
CAST(aloofness AS text) AS attribute3,
null AS attribute4, ...
FROM cat;

So, I guess I'm stuck doing the UNION ALL approach in this instance.
This won't get me the partitioning win, nor the flexibility and
cleanliness of design that I'd get with inheritance.

As far as I can see, supporting the above would it mean adding
inheritance and constraint support to views. Does anyone have a better
approach?

Drew


---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [SQL] Like with special character

[Emi Lu]

I'm using postGre with tables which contain French character 
(?...). Is there a fonction which performs a like in replacing ? 
(e cute) by e ?


to_ascii() should helps you


I got the following error:

select to_ascii('ê');
ERROR:  encoding conversion from UNICODE to ASCII not supported

Some comments about it.



---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

Re: [SQL] hi let me know the solution to this question

[Aaron Bono]

On 7/18/06, Bricklen Anderson <[EMAIL PROTECTED]> wrote:
Aaron Bono wrote:> On 7/18/06, *Michael Fuhr* <[EMAIL PROTECTED] [EMAIL PROTECTED]>> wrote:> 
http://www.postgresql.org/docs/8.1/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-DIAGNOSTICS>> OK, this question got me wondering: is there a way to determine, in a> function/stored procedure, the number of rows that were
> inserted/updated/deleted?> I could not find any documentation about getting the count of updates> but when you run the update by itself, PostgreSQL reports the number of> records updated so I have to believe the information is available
> somewhere.>> Thanks,> Aaron>Look for the section entitled "36.6.6. Obtaining the Result Status" onthe link that Michael Fuhr supplied (above). Is that what you are
looking for?Ah yes, I missed the part on ROW_COUNT .  That should do it.==   Aaron Bono
   Aranya Software Technologies, Inc.   http://www.aranya.com==

Re: [SQL] How to pass array of values to a stored procedure

[Curtis Scheer]

Thanks Tom and Tony for the help, I actually implemented the solution using
a bpchar[] and hopefully the "field1 = ANY (arrayvalue)" syntax will not
hurt the query performance a lot without an index scan. 

Thanks again,
Curtis

On 7/18/06, Tony Wasson <[EMAIL PROTECTED]> wrote:
> On 7/18/06, Curtis Scheer <[EMAIL PROTECTED]> wrote:
> > Does anyone have any examples of how I would make a stored procedure in
> > plpgsql that would allow for passing a list or arrays of values to be
used
> > in an sql IN clause?  Like so: select * from table where field1 in
(values).
> >

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [SQL] Like with special character

[Osvaldo Kussama]

Emi Lu <[EMAIL PROTECTED]> escreveu:  I'm using postGre with tables which contain French character >> (?...). Is there a fonction which performs a like in replacing ? >> (e cute) by e ?>> to_ascii() should helps youI got the following error:select to_ascii('ê');ERROR:  encoding conversion from UNICODE to ASCII not supportedSome comments about it.From the docs:http://www.postgresql.org/docs/8.1/interactive/functions-string.html#FTN.AEN7612Notes on table 9-6. Other String Functions:"The to_ascii function supports conversion from LATIN1, LATIN2, LATIN9, and WIN1250 encodings only. "Try convert encoding:bdteste=# \setVERSION = 'PostgreSQL 8.1.4 on i386-redhat-linux-gnu, compiled by
 GCC i386-redhat-linux-gcc (GCC) 4.1.0 20060304 (Red Hat 4.1.0-3)'ENCODING = 'UTF8'..bdteste=# select to_ascii('ê');ERRO:  conversão de condificação de UTF8 para ASCII não é suportadabdteste=# select to_ascii('ê','LATIN1'); to_ascii-- Aa(1 registro)bdteste=# SELECT convert('ê', 'UTF8', 'LATIN1'); convert-(1 registro)bdteste=# SELECT to_ascii(convert('ê', 'UTF8', 'LATIN1'));ERRO:  conversão de condificação de UTF8 para ASCII não é suportadabdteste=# SELECT to_ascii(convert('ê', 'UTF8', 'LATIN1'), 'LATIN1'); to_ascii-- e(1 registro)[]sOsvaldo 
		 
O Yahoo! está de cara nova. Venha conferir!

[SQL] is there any dataware housing tools for postgresql

[vamsee movva]

Hello all
could you please tell me if there are any dataware housing tools for postgresql
Thanks in advance
vamsee