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

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

Re: [SQL] function based index problem

On Thu, Sep 1, 2011 at 12:09 AM, David Johnston wrote: > ** ** > > *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:*

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 on

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

Re: [SQL] Function To Strip HTML

I found a way to do this using regular expressions. Found this on another website CREATE OR REPLACE FUNCTION strip_tags(TEXT) RETURNS TEXT AS $$ 2SELECT regexp_replace(regexp_replace($1, E'(?x)<[^>]*?(\s alt \s* = \s* ([\'"]) ([^>]*?) \2) [^>]*? >', E'\3'), E'(?x)(< [^>]*? >)', '', 'g') 3$

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.

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

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

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

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

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 Hello All

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

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

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

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

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

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

2008/6/3 maria s <[EMAIL PROTECTED]>: > Hi Pavel Stehule, > Thanks for your reply. > > If I want to return a string and an array how should I do it? > The problem is as I explained before. postgres=# create or replace function foo(j integer, out a varchar, out b varchar[]) as $$ begin a := 'kuku';

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

Hi Pavel Stehule, Thanks for your reply. If I want to return a string and an array how should I do it? The problem is as I explained before. 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, M1...Mn

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

2008/6/3 maria s <[EMAIL PROTECTED]>: > 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 with string and array? postgres=#

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

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 with string and array? Thanks, Maria On Tue, Jun 3, 2008 at 9:57 AM, Ivan Sergio Bo

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

On Tue, 3 Jun 2008 09:41:27 -0400 "maria s" <[EMAIL PROTECTED]> wrote: > 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

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

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

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

Re: [SQL] Function description

I can't install applications on my desktop pc ICT won't install that application on my desktop pc... that would require an amout of trust and... >>> Richard Huxton <[EMAIL PROTECTED]> 2008-02-15 12:17 >>> Well, the windows installer comes with the backend database server, psql.exe, pgadmin, manua

Re: [SQL] Function description

Hi Bart, Bart Degryse wrote: Richard Huxton <[EMAIL PROTECTED]> 2008-02-15 11:50 >>> >>> To see how e.g. \dC etc work start psql with -E >>> >> I'm sorry, but I don't have commandline access to the database. That would >> require an amount of trust and a level of competence our ICT depart

Re: [SQL] Function description

Bart Degryse wrote: Richard Huxton <[EMAIL PROTECTED]> 2008-02-15 11:50 >>> To see how e.g. \dC etc work start psql with -E I'm sorry, but I don't have commandline access to the database. That would require an amount of trust and a level of competence our ICT department is incapable of. Well,

Re: [SQL] Function description

Richard Huxton <[EMAIL PROTECTED]> 2008-02-15 11:50 >>> >>> To see how e.g. \dC etc work start psql with -E >>> >> I'm sorry, but I don't have commandline access to the database. That would >> require an amount of trust and a level of competence our ICT department is >> incapable of. > >Well,

Re: [SQL] Function description

Bart Degryse wrote: Richard Huxton <[EMAIL PROTECTED]> 2008-02-15 11:15 >>> Dont' forget to cc: the list Bart :-) My mistake, sorry. Normally I only reply to the list and not to the respondent's personal address. Would that be wrong too? Well, some people prefer to reply directly from their

Re: [SQL] Function description

>>> Richard Huxton <[EMAIL PROTECTED]> 2008-02-15 11:15 >>> >Dont' forget to cc: the list Bart :-) My mistake, sorry. Normally I only reply to the list and not to the respondent's personal address. Would that be wrong too? > >Bart Degryse wrote: >> Thanks for that explanation Richard. >> But ho

Re: [SQL] Function description

Dont' forget to cc: the list Bart :-) Bart Degryse wrote: Thanks for that explanation Richard. But how can I know what they repeat without having documentation. I mean, how about functions like eg regexeqjoinsel, regexeqsel, reltimeeq, reltimege. I can imagine they mimic some other functionalit

Re: [SQL] Function description

Bart Degryse wrote: Hi all, I find some 1553 functions in pg_catalog of which only a small part is documented in the manual. Does anyone know where I can find what the others do with maybe an example. There's no other documentation (apart from the source) but most are repetitions and conversi

Re: [SQL] Function result using execute

Tom Lane wrote: Do you really need an EXECUTE? If so, maybe you could restructure this using a FOR ... IN EXECUTE, or some such thing. I'll always only ever have a single result since the function gets passes all the fields making up the primary key of the table, so doing a for in seems like

Re: [SQL] Function result using execute

On Dec 11, 2007, at 11:15 PM, Paul Lambert wrote: I have a function which uses execute to populate the value of a variable based on a defined select construct. The relevant part of the code looks like thus: EXECUTE curr_query INTO curr_amount; RAISE NOTICE '%',curr_amount; IF NOT FO

Re: [SQL] Function result using execute

Paul Lambert <[EMAIL PROTECTED]> writes: > The relevant part of the code looks like thus: > EXECUTE curr_query INTO curr_amount; > RAISE NOTICE '%',curr_amount; > IF NOT FOUND THEN >curr_amount=0; > END IF; > ... which suggests to me that although the > execute has populat

Re: [SQL] Function Volatility

Tom Lane writes: > The IMMUTABLE marker is a promise from you to the system that it is safe > to optimize away multiple calls to the function. It is not a promise > from the system to you that the system will expend unlimited amounts of > energy to detect duplicate calls. Nicely put. Thanks! BTW

Re: [SQL] Function Volatility

"Fernando Hevia" <[EMAIL PROTECTED]> writes: > I am not sure if I am understanding volatility. You're not. > What bother me are the 3 "been here" messages. As the function is immutable > and the parameter remains unchanged needs the planner actually execute the > function 3 times? The IMMUTABLE

Re: [SQL] function to find difference between in days between two dates

2007/6/14, A. Kretschmer <[EMAIL PROTECTED]>: am Thu, dem 14.06.2007, um 11:56:15 +0530 mailte Ashish Karalkar folgendes: > Hello all, > > Is there any function to find differences in days between two dates? Yes, age(). there is difference date - date --> integer age(date::timestamp, date

Re: [SQL] function to find difference between in days between two dates

am Thu, dem 14.06.2007, um 11:56:15 +0530 mailte Ashish Karalkar folgendes: > Hello all, > > Is there any function to find differences in days between two dates? Yes, age(). Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FF

Re: [SQL] function to find difference between in days between two dates

Hello PostgreSQL hasn't any official function for it. If you need it, you can write own function CREATE FUNCTION date_diff(date, date) returns integer as $$ select $1-$2; $$ language sql; Regards Pavel Stehule 2007/6/14, Ashish Karalkar <[EMAIL PROTECTED]>: Hello all, Is there any function

Re: [SQL] Function to return a multiple colmn table or view

Don't forget to cc: the list Wilkinson, Jim wrote: Hi Richard, your example worked find , but when I substitue my view into the function and the tale name, I get the following error: ERROR: wrong record type supplied in RETURN NEXT CONTEXT: Pl/pqSQL function "create_view" line 11 at return next

Re: [SQL] Function to return a multiple colmn table or view

Wilkinson, Jim wrote: I am new to psql , so please be patient ! Can someone please provide a small quick example of a a function that take 1 paramater and based on that parameter, returns a table or view ? Etc Note this is just a abstract of the functon, not a working function call !!! C

Re: [SQL] Function returning SETOF using plpythonu

On Monday 29 January 2007 6:12 am, Luís Sousa wrote: > Thanks :-) > That worked fine. > > >plpy.execute returns dictionary, and you need a list. You may try this: > > > >CREATE FUNCTION "test_python_setof"() > >RETURNS SETOF text AS ' > >records=plpy.execute("SELECT name FROM interface"); > >

Re: [SQL] Function returning SETOF using plpythonu

Thanks :-) That worked fine. plpy.execute returns dictionary, and you need a list. You may try this: CREATE FUNCTION "test_python_setof"() RETURNS SETOF text AS ' records=plpy.execute("SELECT name FROM interface"); return [ (r["name"]) for r in records] ' LANGUAGE 'plpythonu'; Then

Re: [SQL] Function returning SETOF using plpythonu

Dnia Fri, 26 Jan 2007 17:24:52 +, Luís Sousa napisał(a): > Hi, > > Is it possible to return rows from a function written in plpythonu using > SETOF? > > Example: > CREATE FUNCTION "test_python_setof"() > RETURNS SETOF text AS ' > records=plpy.execute("SELECT name FROM interface"); >

  1   2   3   >