Re: [SQL] function array_to_string(text[]) does not exist

Hello you should to enter separator postgres=# select array_to_string(ARRAY[1,2,3,4], '|'); array_to_string ─ 1|2|3|4 (1 row) Regards Pavel Stehule 2013/8/25 Victor Sterpu > Hello > > When I run : > SELECT array_to_string(array_agg(CONCAT(CAST (ltrv1.val_min AS CHAR), '-

Re: [SQL] function array_to_string(text[]) does not exist

Yes,array_to_string(text[]) does not exist. This from the doc may help: array_to_string(anyarray,text [, text]) text concatenates array elements using supplied delimiter and optional null stringarray_to_string(ARRAY[1, 2, 3, NULL, 5], ',', '*') 1,2,3,*,5 Jov blog: http:amutu.com/blog

[SQL] function array_to_string(text[]) does not exist

Hello When I run : SELECT array_to_string(array_agg(CONCAT(CAST (ltrv1.val_min AS CHAR), '-', CAST(ltrv1.val_max AS CHAR), ' ', ltrv1.comentarii))) FROM lab_tests_reference_values ltrv1 GROUP BY ltrv1.val_min, ltrv1.val_max, ltrv1.comentarii; I get the error: ERROR: function array_to_string(t

Re: [SQL] Function definitions - batch update

Thanks for the hints, I'll give it a try. pozdrowienia mk 2012/2/21 Tom Lane > Marcin Krawczyk writes: > > I've come across a situation when I need to add some constant code to all > > functions in my database. Does anyone know a way to batch update all > > definitions ? I've got like 500 fu

Re: [SQL] Function definitions - batch update

Marcin Krawczyk writes: > I've come across a situation when I need to add some constant code to all > functions in my database. Does anyone know a way to batch update all > definitions ? I've got like 500 functions so doing it one by one will be > time consuming. If you're feeling like a DBA cowb

[SQL] Function definitions - batch update

Hi list, I've come across a situation when I need to add some constant code to all functions in my database. Does anyone know a way to batch update all definitions ? I've got like 500 functions so doing it one by one will be time consuming. pozdrowienia mk

Re: [SQL] function based index problem

sql.org > *Subject:* [SQL] function based index problem > > ** ** > > Hi, > on table entry (17M records) there is one index: > > CREATE INDEX ndxlen > ON uniprot_frekvencije.entry > USING btree > (length(sequence::text)); > > When using "

Re: [SQL] function based index problem

From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] On Behalf Of Viktor Bojovic Sent: Wednesday, August 31, 2011 5:27 PM To: pgsql-sql@postgresql.org; pgsql-ad...@postgresql.org Subject: [SQL] function based index problem Hi, on table entry (17M records) there is

[SQL] function based index problem

Hi, on table entry (17M records) there is one index: CREATE INDEX ndxlen ON uniprot_frekvencije.entry USING btree (length(sequence::text)); When using ">=" in search which returns only two records, query runs much (hundred times) slower. i don't know why it doesn't use index scan. I just wa

[SQL] Function to total reset a schema

Hi all, I need to write a function that totally empty a schema. So I have written a TRUNCATE statement for each table and set to 0 each sequence. Btw, it could be good to execute a vacuum statement on each table, but from within the function this is not allowed. Is there a way to obtain the s

Re: [SQL] Function to total reset a schema

I would hope you have readily at hand the ddl for the schema in question. Then it's simply a matter of drop schema cascade and re-run you ddl scripts. Surfing wrote: Hi all, I need to write a function that totally empty a schema. So I have written a TRUNCATE statement for each table and set

Re: [SQL] Function to total reset a schema

I have two schemas, so I can't do that ... @ Scott. You are totally right ... I have just read this on the documentation: "TRUNCATE quickly removes all rows from a set of tables. It has the same effect as an unqualified DELETE on each table, but since it does not actually scan the tables it is

Re: [SQL] Function to total reset a schema

Surfing wrote on 29.05.2011 09:38: Hi all, I need to write a function that totally empty a schema. So I have written a TRUNCATE statement for each table and set to 0 each sequence. Btw, it could be good to execute a vacuum statement on each table, but from within the function this is not allow

Re: [SQL] Function to total reset a schema

On Sun, May 29, 2011 at 1:38 AM, Surfing wrote: > Hi all, > I need to write a function that totally empty a schema. > > So I have written a TRUNCATE statement for each table and set to 0 each > sequence. > Btw, it could be good to execute a vacuum statement on each table, but from > within the fun

[SQL] Function to total reset a schema

Hi all, I need to write a function that totally empty a schema. So I have written a TRUNCATE statement for each table and set to 0 each sequence. Btw, it could be good to execute a vacuum statement on each table, but from within the function this is not allowed. Is there a way to obtain the s

[SQL] function timeout

im writting plsh function which will execute some shell commands and return result as varchar to database. problem is that some commands will possibly cause to large timeout or will never stop so i wanted to ask if somehow function can be autokilled if it doesn't finish in time defined for that fu

Re: [SQL] Function To Strip HTML

27;\3'), E'(?x)(< [^>]*? >)', '', 'g') 3$$ LANGUAGE SQL; From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] On Behalf Of Ozer, Pam Sent: Wednesday, February 23, 2011 3:41 PM To: pgsql-sql@postgresql.org Subject: [SQL] Fun

[SQL] Function To Strip HTML

I have the following function that I used in MSSQL. I would like to create the same function in PostgreSql. I think I am a bit confused on how to create and set variables in PostgreSql. Can someone give me a place to start to create something like this? Thanks Pam SET ANSI_NULL

Re: [SQL] Function compile error

Title: Thanks Igor Nayman!!! The function worked for me Sivannarayanareddy Nusum | System Analyst(Moneta GDO)

Re: [SQL] Function compile error

> -Original Message- > From: Sivannarayanreddy [mailto:sivannarayanre...@subexworld.com] > Sent: Wednesday, February 16, 2011 7:36 AM > To: pgsql-sql@postgresql.org > Subject: Function compile error > > Hello, > I am trying to create the function as below but it is > throwing error 'E

Re: [SQL] Function compile error

Hello please, look to page http://www.postgresql.org/docs/9.0/interactive/plpgsql-porting.html It can be faster, if you try to read PL/pgSQL documentation first. PL/pgSQL is near PL/SQL, but it is a different language and environment still. http://www.postgresql.org/docs/9.0/interactive/plpgsql.

[SQL] Function compile error

Title: Hello, I am trying to create the function as below but it is throwing error 'ERROR:  syntax error at or near "DECLARE"', Could some one help me please CREATE FUNCTION check_password(databasename text, tablename text, indexname text)RETURNS V

Re: [SQL] Function Syntax Help

"Plugge, Joe R." writes: > Thanks, I changed my code to this, it compiled, and it seems to be running > now: It looks like you are expecting assignment to the input parameters to do something useful ... it will not. Maybe you need some output parameters? regards, tom la

Re: [SQL] Function Syntax Help

7;1 minute'; newstop := newstop + INTERVAL '1 minute'; END LOOP; END; $$ LANGUAGE 'plpgsql' VOLATILE; From: epai...@googlemail.com [mailto:epai...@googlemail.com] On Behalf Of Brian Modra Sent: Friday, October 30, 2009 2:46 PM To: Plugge, Joe R. Cc: pgsql-sql@

Re: [SQL] Function Syntax Help

dify the new variables... > > > > > > > *From:* epai...@googlemail.com [mailto:epai...@googlemail.com] *On Behalf > Of *Brian Modra > *Sent:* Friday, October 30, 2009 2:29 PM > *To:* Plugge, Joe R. > *Cc:* pgsql-sql@postgresql.org > *Subject:* Re: [SQL] Function Syntax

Re: [SQL] Function Syntax Help

il.com] On Behalf Of Brian Modra Sent: Friday, October 30, 2009 2:29 PM To: Plugge, Joe R. Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] Function Syntax Help 2009/10/30 Plugge, Joe R. mailto:jrplu...@west.com>> I am trying to create a function that will grind through a cdr table and p

Re: [SQL] Function Syntax Help

2009/10/30 Plugge, Joe R. > I am trying to create a function that will grind through a cdr table and > populate another table. I am trying to load the function and am getting the > following error: > > > > ERROR: function result type must be specified > > > > > > CREATE FUNCTION gen_simultaneo

[SQL] Function Syntax Help

I am trying to create a function that will grind through a cdr table and populate another table. I am trying to load the function and am getting the following error: ERROR: function result type must be specified CREATE FUNCTION gen_simultaneous_calls(mystart timestamp, mystop timestamp)

Re: [SQL] Function Anomaly?

Gary Chambers wrote: > CREATE OR REPLACE FUNCTION getnote(INTEGER, BIGINT) RETURNS getnote_t AS > When I call it with a row where n.is_private is TRUE and n.ownerid IS > TRUE, I receive a single row of all null values: > > notesdb=# select * from getnote(1, 2); > When I submit the query directly

[SQL] Function Anomaly?

All... Given the following type and function: CREATE TYPE getnote_t AS (nid BIGINT, ownerid INTEGER, ownername VARCHAR, hostname VARCHAR, entrytime TIMESTAMP, is_active VARCHAR, is_private VARCHAR, notetext TEXT); CREATE OR REPLACE FUNCTION getnote(INTEGER, BIGINT) RETURNS getnote_t AS $getnote$

Re: [SQL] function returning a cursor and a scalar

July 13, 2009 2:22 PM To: Surajit Bhattacharjee Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] function returning a cursor and a scalar Surajit Bhattacharjee wrote: > I am new to Postgresql and am trying to write a function which will do a > search and return the first page of results along

Re: [SQL] function returning a cursor and a scalar

Surajit Bhattacharjee wrote: > I am new to Postgresql and am trying to write a function which will do a > search and return the first page of results along with the total number > of matches. How can I make my function return a cursor AND a scalar - > can I do the scalar as an OUT param and then ma

[SQL] function returning a cursor and a scalar

I am new to Postgresql and am trying to write a function which will do a search and return the first page of results along with the total number of matches. How can I make my function return a cursor AND a scalar - can I do the scalar as an OUT param and then make the function explicitly RETURN a r

Re: [SQL] FUNCTION problem

Adrian Klaver wrote: If you are using Postgres 8.1+ then it becomes even easier because you can use OUT parameters in the function argument list to eliminate the "as test(c1 int,c2 int)" clause. At this point it becomes a A-->B-->C problem i.e determine what your inputs are, how you want to

Re: [SQL] FUNCTION problem

- "Peter Willis" wrote: > Adrian Klaver wrote: > > On Friday 03 April 2009 6:51:05 am Adrian Klaver wrote: > >> On Thursday 02 April 2009 6:16:44 pm Adrian Klaver wrote: > >>> Now I remember. Its something that trips me up, the RECORD in > RETURN > >>> setof RECORD is not the same thing as t

Re: [SQL] FUNCTION problem

Adrian Klaver wrote: On Friday 03 April 2009 6:51:05 am Adrian Klaver wrote: On Thursday 02 April 2009 6:16:44 pm Adrian Klaver wrote: Now I remember. Its something that trips me up, the RECORD in RETURN setof RECORD is not the same thing as the RECORD in DECLARE RECORD. See below for a better

Re: [SQL] FUNCTION problem

On Friday 03 April 2009 6:51:05 am Adrian Klaver wrote: > On Thursday 02 April 2009 6:16:44 pm Adrian Klaver wrote: > > Now I remember. Its something that trips me up, the RECORD in RETURN > > setof RECORD is not the same thing as the RECORD in DECLARE RECORD. See > > below for a better explanation

Re: [SQL] FUNCTION problem

On Thursday 02 April 2009 6:16:44 pm Adrian Klaver wrote: > > > Now I remember. Its something that trips me up, the RECORD in RETURN setof > RECORD is not the same thing as the RECORD in DECLARE RECORD. See below for > a better explanation- > http://www.postgresql.org/docs/8.3/interactive/plpgsql-d

Re: [SQL] FUNCTION problem

On Thursday 02 April 2009 4:22:06 pm Peter Willis wrote: > Adrian Klaver wrote: > > Did you happen to catch this: > > Note that functions using RETURN NEXT or RETURN QUERY must be called as a > > table source in a FROM clause > > > > Try: > > select * from test_function(1) > > I did miss that, but

Re: [SQL] FUNCTION problem

Adrian Klaver wrote: Did you happen to catch this: Note that functions using RETURN NEXT or RETURN QUERY must be called as a table source in a FROM clause Try: select * from test_function(1) I did miss that, but using that method to query the function didn't work either. Postgres doesn't s

Re: [SQL] FUNCTION problem

- "Peter Willis" wrote: > Adrian Klaver wrote: > > On Wednesday 01 April 2009 4:31:20 pm Peter Willis wrote: > >> Hello, > >> > >> I am having a problem with a FUNCTION. > >> The function creates just fine with no errors. > >> > >> However, when I call the function postgres produces an err

Re: [SQL] FUNCTION problem

Adrian Klaver wrote: On Wednesday 01 April 2009 4:31:20 pm Peter Willis wrote: Hello, I am having a problem with a FUNCTION. The function creates just fine with no errors. However, when I call the function postgres produces an error. Perhaps someone can enlighten me. --I can reproduce the e

Re: [SQL] FUNCTION problem

On Wednesday 01 April 2009 4:31:20 pm Peter Willis wrote: > Hello, > > I am having a problem with a FUNCTION. > The function creates just fine with no errors. > > However, when I call the function postgres produces an error. > > Perhaps someone can enlighten me. > > > --I can reproduce the error by

[SQL] FUNCTION problem

Hello, I am having a problem with a FUNCTION. The function creates just fine with no errors. However, when I call the function postgres produces an error. Perhaps someone can enlighten me. --I can reproduce the error by making a test function --that is much easier to follow that the original:

[SQL] Function Returning a Set of Composite Value

Hi All , I have a function f_wrapper(seq,pat) which returns a composite value of type (int,int[][]); I am aware that I can run a query somthing like this to get it working select * from f_wrapper('XYZ,'X') as m1(mid int,match int[][]); but I want to pass another table column as parameter to

Re: [SQL] function - string ends with

If I understand what you need, I guess this clause does work. WHERE string LIKE '%substring' Best, Oliveiros - Original Message - From: Kevin Duffy To: pgsql-sql@postgresql.org Sent: Friday, November 21, 2008 4:30 PM Subject: [SQL] function - string ends with

Re: [SQL] function - string ends with

Take a look at LIKE or ILIKE kd From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Kevin Duffy Sent: Friday, November 21, 2008 11:31 AM To: pgsql-sql@postgresql.org Subject: [SQL] function - string ends with Hello All: Is anyone aware

[SQL] function - string ends with

Hello All: Is anyone aware of a function in PgSQL that will check if a string ends with a specific string? I.e. rposition(substring in string ) returns int Starts searching right to left within string Thanks for your attention to this matter. Kevin Duffy

Re: [SQL] Function Syntax involving pipes and ' marks?

Is that more expensive to run than just useing a bunch of ticks? Sent from Apple iPhone 3G On Sep 10, 2008, at 11:24 AM, Bricklen Anderson <[EMAIL PROTECTED]> wrote: Ruben Gouveia wrote: v_where varchar(256) := 'where m.jb_date < '||p_date + integer '1'||

Re: [SQL] Function Syntax involving pipes and ' marks?

i will try that. thank you On Wed, Sep 10, 2008 at 11:45 AM, Tom Lane <[EMAIL PROTECTED]> wrote: > Bricklen Anderson <[EMAIL PROTECTED]> writes: > > Ruben Gouveia wrote: > >> Is that more expensive to run than just useing a bunch of ticks? > > > I personally have never noticed any increased overh

Re: [SQL] Function Syntax involving pipes and ' marks?

Bricklen Anderson <[EMAIL PROTECTED]> writes: > Ruben Gouveia wrote: >> Is that more expensive to run than just useing a bunch of ticks? > I personally have never noticed any increased overhead from quote_literal. Much more important is that you'll reliably get the right answer.

Re: [SQL] Function Syntax involving pipes and ' marks?

Ruben Gouveia wrote: Is that more expensive to run than just useing a bunch of ticks? Try wrapping your p_date in a quote_literal like ... 'where m.jb_date < '||quote_literal(p_date+INTEGER '1')||' and ... I personally have never noticed any increased overhead from quote_literal. -- Sent vi

Re: [SQL] Function Syntax involving pipes and ' marks?

Ruben Gouveia wrote: v_where varchar(256) := 'where m.jb_date < '||p_date + integer '1'|| ' and m.jb_date >='||p_date||''; Try wrapping your p_date in a quote_literal like ... 'where m.jb_date < '||quote_literal(p_date+INTEGER '1')||' and ... eg. CREATE OR REPL

Re: [SQL] Function Syntax involving pipes and ' marks?

Ruben Gouveia escribió: > No matter how many times i try, i can't seem to get the write amount of ' > marks around the date parameters in my v_where declaration. What am i doing > wrong here? Apparently you're not aware that you can nest the $$ quote marks. You could just use $a$ to assign to the

[SQL] Function Syntax involving pipes and ' marks?

No matter how many times i try, i can't seem to get the write amount of ' marks around the date parameters in my v_where declaration. What am i doing wrong here? v_stmt should look like this if done correctly: select count(distinct m.id) from (select id, greatest(max(last_p),max(last_b)) as date_

Re: [SQL] Function syntax ?

It appears there is already a greatest() and least() function available...so no need for creating this function. On Tue, Sep 9, 2008 at 11:16 AM, Ruben Gouveia <[EMAIL PROTECTED]> wrote: > thanks pavel...that worked! I like the simplicity of your first suggestion. > > > On Tue, Sep 9, 2008 at 11:

Re: [SQL] Function syntax ?

thanks pavel...that worked! I like the simplicity of your first suggestion. On Tue, Sep 9, 2008 at 11:05 AM, Pavel Stehule <[EMAIL PROTECTED]>wrote: > try > > create or replace function fcn_max_dt(p_dt timestamp without time zone, > p_dt2 timestamp without ti

Re: [SQL] Function syntax ?

Scott Marlowe wrote: > On Tue, Sep 9, 2008 at 11:55 AM, Ruben Gouveia <[EMAIL PROTECTED]> wrote: >> Does this syntax look correct? Can anyone think of a better way to write >> this? >> >> This function will accept two timestamp parameters and determine the highest >> of the two? [snip] > It certain

Re: [SQL] Function syntax ?

That's not what I copied and pasted in. Leave out the v_dt := p_dt; > v_dt2 := p_dt2; lines and turn the v into p in the rest of the function. On Tue, Sep 9, 2008 at 12:11 PM, Ruben Gouveia <[EMAIL PROTECTED]> wrote: > When i tried that, i got the following error: > > create or replace fu

Re: [SQL] Function syntax ?

try create or replace function fcn_max_dt(p_dt timestamp without time zone, p_dt2 timestamp without time zone) returns imestamp without time zone as $$ select greatest($1,$2); $$ language sql; or begin return greatest(p_dt, p_dt2); end; $$ language plpgsq

Re: [SQL] Function syntax ?

When i tried that, i got the following error: create or replace function fcn_max_dt(p_dt timestamp without time zone, p_dt2 timestamp without time zone) returns timestamp without time zone as $$ BEGIN v_dt := p_dt; v_dt2 := p_dt2; if v_dt >= v_

Re: [SQL] Function syntax ?

On Tue, Sep 9, 2008 at 11:55 AM, Ruben Gouveia <[EMAIL PROTECTED]> wrote: > Does this syntax look correct? Can anyone think of a better way to write > this? > > This function will accept two timestamp parameters and determine the highest > of the two? > > create or replace function fcn_max_dt(p_dt

[SQL] Function syntax ?

Does this syntax look correct? Can anyone think of a better way to write this? This function will accept two timestamp parameters and determine the highest of the two? create or replace function fcn_max_dt(p_dt timestamp without time zone, p_dt2 timestamp wit

Re: [SQL] Function returning setof taking parameters from another table

Marcin Stępnicki wrote: > So far the only method I can think of is to use union all with > different parametrs, like: > > select * from f_test(123) > union all > select * from f_test(124) > union all > select * from f_test(125); > > But it is not flexible, I'd like to have parameters stored in a

[SQL] Function returning setof taking parameters from another table

Hello. I've got a function which returns set of records: select * from f_test(123); param | val1 | val2 --- 123 | 1 | 17 123 | 2 | 18 I'd like to execute it multiple times with parameters from other query, like (it doesn't work of course): select *

Re: [SQL] function that returns a set of records and integer(both of them)‏

ords i'll > use sql and when i have to do stored procedure of functions i'll use plpsql > in this case but you're telling me that it will change in the 8.4 version. > won't it? > > > > >> Date: Sun, 13 Jul 2008 07:06:07 +0200 >> From: [EMAIL PROTECTED

RE: [SQL] function that returns a set of records and int eger(both of them)‏

:07 +0200> From: [EMAIL PROTECTED]> To: [EMAIL > PROTECTED]> Subject: Re: [SQL] function that returns a set of records and > integer(both of them)‏> CC: pgsql-sql@postgresql.org> > Hello> > 2008/7/13 > daniel blanco <[EMAIL PROTECTED]>:> > Ok, than

Re: [SQL] function that returns a set of records and integer(both of them)‏

27;t support global (session variables) - this topic was discussed in different thread this week Regards Pavel > as you see i do a select and a return at the same time when de sw variable > is > than 0. i was expecting to do a similiar function with plpgsql o sql in > postgresql >

RE: [SQL] function that returns a set of records and int eger(both of them)‏

time when de sw variable is > than 0. i was expecting to do a similiar function with plpgsql o sql in postgresql > Date: Sat, 12 Jul 2008 09:05:45 +0200> From: [EMAIL PROTECTED]> To: [EMAIL > PROTECTED]> Subject: Re: [SQL] function that returns a set of records and > integer(bot

Re: [SQL] function that returns a set of records and integer(both of them)‏

2008/7/11 daniel blanco <[EMAIL PROTECTED]>: > Hi Everyone > > I would like to know if i can create a function that returns a set of record > with the sql statement: select and a integer, i mean both of them, because i > remenber that in sql server (transact sql) i can do that in a stored > procedu

[SQL] function that returns a set of records and integer(both of them)‏

Hi Everyone I would like to know if i can create a function that returns a set of record with the sql statement: select and a integer, i mean both of them, because i remenber that in sql server (transact sql) i can do that in a stored procedure doing a select statement and a return of a integer

Re: [SQL] function returning result set of varying column

the number of columns that I am going to >> >> > return. >> >> > >> >> > I have 2 tables. For a single entry E1 in one table(t1), I have >> >> > to fetch all the matching entries for E1 from the other >> >> >

Re: [SQL] function returning result set of varying column

On Tue, 3 Jun 2008 10:06:45 -0400 "maria s" <[EMAIL PROTECTED]> wrote: > Hi Ivan, > If I have to know the column names then I can't use the Functions. > As I said before, the columns will vary. or As Pavel Stehule said > I will use arrays. > > Is anyone can show an example of returning a record w

Re: [SQL] function returning result set of varying column

t; >> > get. > >> > > >> > Is it possible to write a function that returns this kind of > >> > result? > >> > >> Up to my knowledge as Bart wrote in pl/pgsql you'll have to specify > >> somewhere the return type: in th

Re: [SQL] function returning result set of varying column

should return E1, >> > K1..Kn. So I don't know the number of columns that I am going to >> > get. >> > >> > Is it possible to write a function that returns this kind of >> > result? >> >> Up to my knowledge as Bart wrote in pl/pgsql yo

Re: [SQL] function returning result set of varying column

> > get. > > > > Is it possible to write a function that returns this kind of > > result? > > Up to my knowledge as Bart wrote in pl/pgsql you'll have to specify > somewhere the return type: in the function or in the select calling > the function. > If yo

Re: [SQL] function returning result set of varying column

to my knowledge as Bart wrote in pl/pgsql you'll have to specify somewhere the return type: in the function or in the select calling the function. If you use sql (not pl/pgsql) function you shouldn't be obliged to specify the return type. But I haven't written enough sql function to actua

Re: [SQL] function returning result set of varying column

2008/6/3 maria s <[EMAIL PROTECTED]>: > Thanks for all your replies. > > Actually I don't know the number of columns that I am going to return. > > I have 2 tables. For a single entry E1 in one table(t1), I have to fetch > all the matching entries for E1 from the other table(t2), K1,..Kn. > and f

Re: [SQL] function returning result set of varying column

Thanks for all your replies. Actually I don't know the number of columns that I am going to return. I have 2 tables. For a single entry E1 in one table(t1), I have to fetch all the matching entries for E1 from the other table(t2), K1,..Kn. and finally the function should return E1, K1..Kn. So I

Re: [SQL] function returning result set of varying column

On Tue, 3 Jun 2008 09:01:02 -0400 "maria s" <[EMAIL PROTECTED]> wrote: > Hi Friends, > Thanks for all your for the reply. > > I tried the function and when I execute it using > select * from myfunction() > it says > ERROR: a column definition list is required for functions > returning "record" >

Re: [SQL] function returning result set of varying column

As I wrote before you will have to define your fields when querying the function, eg. select * from myfunction() as ("field1" integer, "field2" text, ...) So suppose you have a table like this CREATE TABLE sometable ( "id" serial, "sometextfield" text, "aninteger" int, "andavarchar" varcha

Re: [SQL] function returning result set of varying column

Hi Friends, Thanks for all your for the reply. I tried the function and when I execute it using select * from myfunction() it says ERROR: a column definition list is required for functions returning "record" Could you please help me to fix this error? Thanks so much for your help. -maria On T

Re: [SQL] function returning result set of varying column

Hi Maria, Try something like CREATE OR REPLACE FUNCTION myfunction() RETURNS SETOF RECORD AS $body$ DECLARE rec record; BEGIN FOR rec IN ( SELECT * FROM sometable) LOOP RETURN NEXT rec; END LOOP; RETURN; END; $body$ LANGUAGE 'plpgsql' VOLATILE; As you can see, the number and typ

[SQL] function returning result set of varying column

Hi friends, I am very new to plsql. I have to write a function that quries few tables and returns a resultset of varying column. In that case I cannot predefine the table with column. If I use RETURNS SETOF then I should know the number of columns and its type?! Is there anyway to return a res

[SQL] Function returns error (view) (RESOLVED)

Hi I discovered that when a person did not change the password, there is no information into change_user_password table, then a exception raise but wasn't treated. Now it is OK. Thanks for all CREATE OR REPLACE FUNCTION seach_password(USER_FOO varchar(100)) RETURNS SETOF vw_change_password AS

Re: [SQL] Function returns error (view) (RESOLVED)

Hi I discovered that when a person did not change the password, there is no information into change_user_password table, then a exception raise but wasn't treated. Now it is OK. Thanks for all CREATE OR REPLACE FUNCTION seach_password(USER_FOO varchar(100)) RETURNS SETOF vw_change_password AS

Re: [SQL] Function returns error (view)

Please send the complete DDL for your function and the tables it uses. Also inform us of the database version you're using. >>> "Professor Flávio Brito" <[EMAIL PROTECTED]> 2008-02-27 21:42 >>> Hi After I did it I received it SELECT * FROM search_password('Paul'); ERROR: set-valued functi

Re: [SQL] Function returns error (view)

Hi After I did it I received it SELECT * FROM search_password('Paul'); ERROR: set-valued function called in context that cannot accept a set SQL state: 0A000 Context: PL/pgSQL function "search_password(" line 14 at return next Error at WHERE login= Paul ?? Thanks for your help Flávio 2008/

Re: [SQL] Function returns error (view)

2008/2/27, Bart Degryse <[EMAIL PROTECTED]>: > > > For rather "simple" queries like this one PostgreSQL indeed seems to be > quite smart. > I have quite a lot of statements where it does make a difference though > (PostgreSQL 8.2.4). I would rather find a situation where an explicit join is planne

Re: [SQL] Function returns error (view)

For rather "simple" queries like this one PostgreSQL indeed seems to be quite smart. I have quite a lot of statements where it does make a difference though (PostgreSQL 8.2.4). As long as I have one statement where it makes a difference I will use the join rather than the IN(subselect) just to b

Re: [SQL] Function returns error (view)

2008/2/27, Bart Degryse <[EMAIL PROTECTED]>: > > I would also suggest you replace the > ...t.cod_user IN (subselect) > by a join construction. I think it's more performant. In recent versions PostgreSQL is quite smart when planning IN, so that shouldn't be a concern. Markus -- Markus Bertheau B

Re: [SQL] Function returns error (view)

How do you call your function? You should call it like this: SELECT * FROM seach_password('Flavio'); Replace Flavio with the login of someone in table_user. Also watch out for the function name: if you copied my suggestion it is seach_... and not search_... I would also suggest you replace the

Re: [SQL] Function returns error (view)

Hi After I did it I received it ERROR: set-valued function called in context that cannot accept a set SQL state: 0A000 Context: PL/pgSQL function "seach_password(" line 14 at return next Error at WHERE login= USER_FOO ?? Thanks for your help Flávio 2008/2/26, Bart Degryse <[EMAIL PROTECTED]>:

Re: [SQL] Function returns error (view)

I think you have a quoting problem You want something like WHERE login= 'Flavo' But you're making something like WHERE login = Flavo Something like this should work... CREATE OR REPLACE FUNCTION seach_password(USER_FOO IN table_user.login%TYPE) RETURNS SETOF vw_change_password AS $BODY$ DECL

Re: [SQL] Function returns error (view)

Hi Colin When I translated from Portuguese to English I forgot a letter, but using the corrected name I received an error. 2008/2/26, Colin Wetherbee <[EMAIL PROTECTED]>: > > Professor Flávio Brito wrote: > > When I Test my view I receive > > > > SELECT seach_password('user_login_foo') > > [

Re: [SQL] Function returns error (view)

Professor Flávio Brito wrote: When I Test my view I receive SELECT seach_password('user_login_foo') [...] ERROR: column "user_login_foo" does not exist SQL state: 42703 Context: PL/pgSQL function "search_password" line 14 at for over execute statement seach_password and search_password are

[SQL] Function returns error (view)

Hi Don't know why I can't receive a return like my view fields (I'm newbie in plpgsql). Postgresql returns me a erro . How can I received a answer like my view structure? When I Test my view I receive SELECT seach_password('user_login_foo') My view returns me 25746;"MARCELO ";"bio1";"bio1";"2

Re: [SQL] Function description

>>> Gavin 'Beau' Baumanis <[EMAIL PROTECTED]> 2008-02-15 13:33 >>> >I MUST have a local / development database for testing and educational / >learning purposes - unless of course you would like me to use the production >server for testing? I do have a development database, but not locally. And

Re: [SQL] Function description

Bart, You just need to put forward an appropriate case. It isn't a case of I would like these things. It is, I MUST have these things in order to perform my job. I MUST have a local / development database for testing and educational / learning purposes - unless of course you would like me to

Re: [SQL] Function description

>>> Gavin 'Beau' Baumanis <[EMAIL PROTECTED]> 2008-02-15 12:46 >>> >The windows installer, available at; >http://www.postgresql.org/ftp/win32/ >Allows you to install the DB and / OR the tools including psql >So you can just install the psql command line tool onto your local machine. I can't instal

  1   2   3   4   >