Re: [GENERAL] Function

On 26/10/17 15:00, Marcio Farah wrote: Good morning for all I´m beginer in PL/pgSQL functions and I have one difficulty. The function bellow should return many records but return just one. The loop just do the first INSERT INTO and get out. If you run SELECT substring(cd_geocmu,1,6)::int

Re: [GENERAL] Function to return per-column counts?

On Thu, Sep 28, 2017 at 3:31 PM, Seamus Abshere wrote: > > > > Does anybody have a function lying around (preferably pl/pgsql) that > > > > takes a table name and returns coverage counts? > > > > > > What is "coverage count"? > > Ah, I should have explained better. I meant how much of a column is

Re: [GENERAL] Function to return per-column counts?

> > > Does anybody have a function lying around (preferably pl/pgsql) that > > > takes a table name and returns coverage counts? > > > > What is "coverage count"? Ah, I should have explained better. I meant how much of a column is null. Basically you have to 0. count how many total records in a

Re: [GENERAL] Function to return per-column counts?

On Thu, Sep 28, 2017 at 12:15 PM, Tomas Vondra wrote: > > > On 09/28/2017 04:34 PM, Seamus Abshere wrote: > > hey, > > > > Does anybody have a function lying around (preferably pl/pgsql) that > > takes a table name and returns coverage counts? > > > > What is "coverage count"? > ​I'm guessing it

Re: [GENERAL] Function to return per-column counts?

On 09/28/2017 04:34 PM, Seamus Abshere wrote: > hey, > > Does anybody have a function lying around (preferably pl/pgsql) that > takes a table name and returns coverage counts? > What is "coverage count"? cheers -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Developme

Re: [GENERAL] Function not inserting rows

Hello, On Wed, 2017-08-23 at 17:23 +0200, Frank Foerster wrote: > > > > But the created statement looks syntax-wise identical to the pgadmin- > statement (except for the forced error of course): > > select * from api_dev.add_texts_to_item( 444, array['PGADM1', > 'PGADM2'] ); > > > I don't

Re: [GENERAL] Function not inserting rows

Thanks, that was it. I did not commit as i was calling "only" a select-statement. Thanks 2017-08-23 18:20 GMT+02:00 Daniele Varrazzo : > On Wed, Aug 23, 2017 at 4:23 PM, Frank Foerster > wrote: > > > Any ideas ? > > commit? > > -- Daniele >

Re: [GENERAL] Function not inserting rows

On Wed, Aug 23, 2017 at 4:23 PM, Frank Foerster wrote: > Any ideas ? commit? -- Daniele -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Function not inserting rows

On Wed, Aug 23, 2017 at 8:23 AM, Frank Foerster wrote: > > sql = "select * from api_dev.add_texts_to_item( %s, %s ); x x" > i get the following python-error: > psycopg2.ProgrammingError: FEHLER: Syntaxfehler bei »s« > LINE 1: ...dd_texts_to_item( 1234, ARRAY['PSYCOPG1', 'PSYCOPG2'] ); s

Re: [GENERAL] Function with limit and offset - PostgreSQL 9.3

On 6/8/2017 6:36 PM, marcinha rocha wrote: |UPDATEtablea a SETmigrated =yes WHEREa.id =row.id;| On my original select, the row will have migrated = false. Maybe All I need to put is a limit 2000 and the query will do the rest? SELECT does not return data in any determinate order unless you us

Re: [GENERAL] Function with limit and offset - PostgreSQL 9.3

On Thursday, June 8, 2017, marcinha rocha wrote: > On Thursday, June 8, 2017, marcinha rocha hotmail.com > > > wrote: > >> On my original select, the row will have migrated = false. Maybe All I >> need to put is a limit 2000 and the query will do the rest? >> >> > You shoud try to avoid the for

Re: [GENERAL] Function with limit and offset - PostgreSQL 9.3

On Thursday, June 8, 2017, marcinha rocha mailto:marciaestefanidaro...@hotmail.com>> wrote: On my original select, the row will have migrated = false. Maybe All I need to put is a limit 2000 and the query will do the rest? You shoud try to avoid the for loop, Why? but yes a limit 2000 on the

Re: [GENERAL] Function with limit and offset - PostgreSQL 9.3

On Thursday, June 8, 2017, marcinha rocha wrote: > On my original select, the row will have migrated = false. Maybe All I > need to put is a limit 2000 and the query will do the rest? > > You shoud try to avoid the for loop, but yes a limit 2000 on the for loop query should work since the migrate

Re: [GENERAL] Function with limit and offset - PostgreSQL 9.3

On 6/8/2017 5:53 PM, marcinha rocha wrote: > Hi guys! I have the following queries, which will basically select > data, insert it onto a new table and update a column on the original > table. I'm sure your example is a gross simplification of what you're really doing, but if that's really all yo

Re: [GENERAL] Function with limit and offset - PostgreSQL 9.3

On 6/8/2017 5:53 PM, marcinha rocha wrote: Hi guys! I have the following queries, which will basically select data, insert it onto a new table and update a column on the original table. I'm sure your example is a gross simplification of what you're really doing, but if that's really all you'

Re: [GENERAL] Function with limit and offset - PostgreSQL 9.3

On Thursday, June 8, 2017, marcinha rocha wrote: > When I call the function, it must execute 2000 rows and then stop. Then > when calling it again, it must start from 2001 to 4000, and so on > > You can do this is with plain sql with the help of a CTE. Insert into + Select ... limit 2000 returni

Re: [GENERAL] Function PostgreSQL 9.2

Just an update here: IF (TG_OP = 'INSERT') THEN > UPDATE public.companies SET client_code_increment = > (client_code_increment + 1) WHERE id = NEW.company_id; The line above was updating the client_code_increment even if the customer was inserting data by hiimself, which is wrong. The cl

Re: [GENERAL] Function PostgreSQL 9.2

drum.lu...@gmail.com wrote: It's working now... Final code: ALTER TABLE public.companies ADD COLUMN client_code_increment integer; ALTER TABLE public.companies ALTER COLUMN client_code_increment SET NOT NULL; ALTER TABLE public.companies ALTER COLUMN client_code_increment SET DEFAU

Re: [GENERAL] Function PostgreSQL 9.2

It's working now... Final code: ALTER TABLE public.companies ADD COLUMN client_code_increment integer; > ALTER TABLE public.companies ALTER COLUMN client_code_increment SET NOT > NULL; > ALTER TABLE public.companies ALTER COLUMN client_code_increment SET > DEFAULT 1000; > COMMIT TRANSACTION; > >

Re: [GENERAL] Function PostgreSQL 9.2

David G. Johnston wrote: ​Berend already identified the problem for you. Thank you. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Function PostgreSQL 9.2

On Thu, May 5, 2016 at 6:17 PM, David G. Johnston < david.g.johns...@gmail.com> wrote: > > If I change that to company_id, I get the error: column "company_id" does >> not exist, because that column is inside USERS and not COMPANIES. >> >> > ​change that what is "that" > > ​Provide the actua

Re: [GENERAL] Function PostgreSQL 9.2

> If I change that to company_id, I get the error: column "company_id" does > not exist, because that column is inside USERS and not COMPANIES. > > ​change that what is "that" ​Provide the actual code you ran that resulted in "column "company_id" does not exist"​ Your attempts at brevity ar

Re: [GENERAL] Function PostgreSQL 9.2

> > > > 2) You have a where clause: company_id = NEW.id >> 3) NEW refers to users >> 4) NEW.id is obstensibly a USER ID >> > > > No... > > ​Which one of the three do you disagree with? ​

Re: [GENERAL] Function PostgreSQL 9.2

> > > 1) You attached users_code_seq() to a trigger on the users table. > yes > 2) You have a where clause: company_id = NEW.id > 3) NEW refers to users > 4) NEW.id is obstensibly a USER ID > No... CREATE OR REPLACE FUNCTION users_code_seq() >RETURNS "trigger" AS $$ > DECLARE code charac

Re: [GENERAL] Function PostgreSQL 9.2

On Thu, May 5, 2016 at 1:22 PM, drum.lu...@gmail.com wrote: > > > On 6 May 2016 at 02:29, David G. Johnston > wrote: > >> On Thu, May 5, 2016 at 3:54 AM, Alban Hertroys >> wrote: >> >>> >>> > On 05 May 2016, at 8:42, drum.lu...@gmail.com wrote: >>> >>> > The final function code is: >>> > >>> >

Re: [GENERAL] Function PostgreSQL 9.2

On 6 May 2016 at 02:29, David G. Johnston wrote: > On Thu, May 5, 2016 at 3:54 AM, Alban Hertroys wrote: > >> >> > On 05 May 2016, at 8:42, drum.lu...@gmail.com wrote: >> >> > The final function code is: >> > >> > CREATE OR REPLACE FUNCTION users_code_seq() >> >RETURNS "trigger" AS $$ >> > D

Re: [GENERAL] Function PostgreSQL 9.2

On Thu, May 5, 2016 at 3:54 AM, Alban Hertroys wrote: > > > On 05 May 2016, at 8:42, drum.lu...@gmail.com wrote: > > > The final function code is: > > > > CREATE OR REPLACE FUNCTION users_code_seq() > >RETURNS "trigger" AS $$ > > DECLARE code character varying; > > BEGIN > > IF NEW.co

Re: [GENERAL] Function PostgreSQL 9.2

> On 05 May 2016, at 8:42, drum.lu...@gmail.com wrote: > The final function code is: > > CREATE OR REPLACE FUNCTION users_code_seq() >RETURNS "trigger" AS $$ > DECLARE code character varying; > BEGIN > IF NEW.code IS NULL THEN > SELECT client_code_increment INTO STRICT NEW.co

Re: [GENERAL] Function PostgreSQL 9.2

> > >> SELECT client_code_increment INTO STRICT NEW.code FROM >> public.companies WHERE id = >> NEW.id ORDER BY client_code_increment DESC; >> > > > > I am pretty sure the above line is wrong. NEW.id refers to users.id, not > the companies.id. Also, the implementation presents a po

Re: [GENERAL] Function PostgreSQL 9.2

drum.lu...@gmail.com wrote: I'm just having some problem when doing: INSERT INTO public.users (id,email,encrypted_password,sign_in_count,created_at,updated_at,company_id) VALUES (66,'tes...@test.com ','password','0','2016-05-03 00:01:01','2016-05-03 00:

Re: [GENERAL] Function PostgreSQL 9.2

I'm just having some problem when doing: INSERT INTO public.users > (id,email,encrypted_password,sign_in_count,created_at,updated_at,company_id) > VALUES (66,'tes...@test.com','password','0','2016-05-03 > 00:01:01','2016-05-03 00:01:01','15'); - see that I'm not providing the "code" column value

Re: [GENERAL] Function PostgreSQL 9.2

On 5/4/2016 9:56 PM, drum.lu...@gmail.com wrote: If I comment the line: *ELSEIF NEW.code IS NULL THEN*, the data is inserted into the users.code column. in the table definition, whats the default value of 'code' ? -- john r pierce, recycling bits in santa cruz

Re: [GENERAL] Function PostgreSQL 9.2

On 5 May 2016 at 16:56, drum.lu...@gmail.com wrote: > CREATE OR REPLACE FUNCTION users_code_seq() >>RETURNS "trigger" AS $$ >> BEGIN >> >> IF (TG_OP = 'INSERT') THEN >> UPDATE public.company_seqs SET last_seq = (last_seq + 1) WHERE >> company_id = NEW.company_id; >> >>

Re: [GENERAL] Function PostgreSQL 9.2

> > CREATE OR REPLACE FUNCTION users_code_seq() >RETURNS "trigger" AS $$ > BEGIN > > IF (TG_OP = 'INSERT') THEN > UPDATE public.company_seqs SET last_seq = (last_seq + 1) WHERE > company_id = NEW.company_id; > > ELSEIF NEW.code IS NULL THEN > SELECT last_seq INTO

Re: [GENERAL] Function PostgreSQL 9.2

David G. Johnston wrote: On Wed, May 4, 2016 at 2:57 PM, drum.lu...@gmail.com ... I would expect a minimum of respect from the members of this list, but seems you got none. If someone would need my help, I'd never insult him/her like you guys are doing. If my questions are too "

Re: [GENERAL] Function PostgreSQL 9.2

On Wed, May 4, 2016 at 2:57 PM, drum.lu...@gmail.com wrote: > >> >> 1) I just may be over-sensitive to this, but after Adrian Klaver referred >> you to a ten-years old post that the above looks an awful lot similar too, >> it sure would be nice to see some attribution >> rather than claiming it a

Re: [GENERAL] Function PostgreSQL 9.2

> > > > 1) I just may be over-sensitive to this, but after Adrian Klaver referred > you to a ten-years old post that the above looks an awful lot similar too, > it sure would be nice to see some attribution > rather than claiming it as your own with "...what *I* did..." > I would expect a minimum

Re: [GENERAL] Function PostgreSQL 9.2

On Tuesday, May 3, 2016, drum.lu...@gmail.com mailto:drum.lu...@gmail.com>> wrote: * This is what I did... |-- Creating the table CREATE TABLE public.company_seqs (company_id BIGINTNOT NULL, last_seq BIGINTNOT NULL DEFAULT

Re: [GENERAL] Function PostgreSQL 9.2

On Tuesday, May 3, 2016, drum.lu...@gmail.com wrote: > >- This is what I did... > > -- Creating the tableCREATE TABLE public.company_seqs(company_id BIGINT NOT > NULL, > last_seq BIGINT NOT NULL DEFAULT 1000,CONSTRAINT company_seqs_pk PRIMARY KEY > (company_id)); > > -- Creating the functio

Re: [GENERAL] Function PostgreSQL 9.2

- This is what I did... -- Creating the tableCREATE TABLE public.company_seqs(company_id BIGINT NOT NULL, last_seq BIGINT NOT NULL DEFAULT 1000,CONSTRAINT company_seqs_pk PRIMARY KEY (company_id)); -- Creating the function CREATE OR REPLACE FUNCTION users_code_seq() RETURNS "trigger" AS' BE

Re: [GENERAL] Function PostgreSQL 9.2

From: David G. Johnston Sent: Tuesday, May 03, 2016 2:46 PM To: drum.lu...@gmail.com …The only other reasonable option is change your model and requirements to something less complex. Seriously, get yourself the books I have recommended and study them BEFORE you continue attempting to

Re: [GENERAL] Function PostgreSQL 9.2

On Tue, May 3, 2016 at 5:06 PM, Melvin Davidson wrote: > > > On Tue, May 3, 2016 at 7:53 PM, drum.lu...@gmail.com > wrote: > >> >>> >>> I agree that having thousands of sequences can be hard to manage, >>> especially in a function, but you did not state that fact before, >>> only that you wanted

Re: [GENERAL] Function PostgreSQL 9.2

On Tue, May 3, 2016 at 7:53 PM, drum.lu...@gmail.com wrote: > >> >> I agree that having thousands of sequences can be hard to manage, >> especially in a function, but you did not state that fact before, >> only that you wanted separate sequences for each company. That >> being said, here is an al

Re: [GENERAL] Function PostgreSQL 9.2

> > > > I agree that having thousands of sequences can be hard to manage, > especially in a function, but you did not state that fact before, > only that you wanted separate sequences for each company. That > being said, here is an alternate solution. > Yep.. that was my mistake. > > 1. CREATE T

Re: [GENERAL] Function PostgreSQL 9.2

On Tue, May 3, 2016 at 5:53 PM, Adrian Klaver wrote: > On 05/03/2016 02:27 PM, drum.lu...@gmail.com wrote: > >> >> >> On 4 May 2016 at 01:18, Melvin Davidson > > wrote: >> >> >> >> On Tue, May 3, 2016 at 1:21 AM, David G. Johnston >> mailto:david.g.johns...@gm

Re: [GENERAL] Function PostgreSQL 9.2

On 05/03/2016 02:27 PM, drum.lu...@gmail.com wrote: On 4 May 2016 at 01:18, Melvin Davidson mailto:melvin6...@gmail.com>> wrote: On Tue, May 3, 2016 at 1:21 AM, David G. Johnston mailto:david.g.johns...@gmail.com>> wrote: Well.. I don't need to add a constraint if I alr

Re: [GENERAL] Function PostgreSQL 9.2

On Tue, May 3, 2016 at 2:27 PM, drum.lu...@gmail.com wrote: > On 4 May 2016 at 01:18, Melvin Davidson wrote: >> >> On Tue, May 3, 2016 at 1:21 AM, David G. Johnston < >> david.g.johns...@gmail.com> wrote: >> >>> Well.. I don't need to add a constraint if I already have a default value,

Re: [GENERAL] Function PostgreSQL 9.2

On 05/03/2016 02:27 PM, drum.lu...@gmail.com wrote: On 4 May 2016 at 01:18, Melvin Davidson mailto:melvin6...@gmail.com>> wrote: On Tue, May 3, 2016 at 1:21 AM, David G. Johnston mailto:david.g.johns...@gmail.com>> wrote: Well.. I don't need to add a constraint if I alr

Re: [GENERAL] Function PostgreSQL 9.2

On 4 May 2016 at 01:18, Melvin Davidson wrote: > > > On Tue, May 3, 2016 at 1:21 AM, David G. Johnston < > david.g.johns...@gmail.com> wrote: > >> >>> Well.. I don't need to add a constraint if I already have a default >>> value, that's right... >>> >> >> Wrong >> >> David J. >> > > What you need

Re: [GENERAL] Function PostgreSQL 9.2

On Tue, May 3, 2016 at 1:21 AM, David G. Johnston < david.g.johns...@gmail.com> wrote: > >> Well.. I don't need to add a constraint if I already have a default >> value, that's right... >> > > Wrong > > David J. > What you need is a TRIGGER function & TRIGGER that will select and assign the next

Re: [GENERAL] Function PostgreSQL 9.2

> > > Well.. I don't need to add a constraint if I already have a default value, > that's right... > Wrong David J.

Re: [GENERAL] Function PostgreSQL 9.2

On 3 May 2016 at 12:44, drum.lu...@gmail.com wrote: > This is what I've done: > > > -- 1 - Creating the Sequence: > > CREATE SEQUENCE users_code_seq >> INCREMENT 1 >> MINVALUE 1 >> MAXVALUE 9223372036854775807 >> START 1000; >> CACHE 1; > > > -- 2 - Setting the DEFAULT > >

Re: [GENERAL] Function PostgreSQL 9.2

On Mon, May 2, 2016 at 5:44 PM, drum.lu...@gmail.com wrote: > This is what I've done: > > > -- 1 - Creating the Sequence: > > CREATE SEQUENCE users_code_seq >> INCREMENT 1 >> MINVALUE 1 >> MAXVALUE 9223372036854775807 >> START 1000; >> CACHE 1; > > > -- 2 - Setting the DEF

Re: [GENERAL] Function PostgreSQL 9.2

This is what I've done: -- 1 - Creating the Sequence: CREATE SEQUENCE users_code_seq > INCREMENT 1 > MINVALUE 1 > MAXVALUE 9223372036854775807 > START 1000; > CACHE 1; -- 2 - Setting the DEFAULT ALTER TABLE public.users ALTER COLUMN code SET DEFAULT > NEXTVAL('user

Re: [GENERAL] Function PostgreSQL 9.2

On Wed, Apr 20, 2016 at 03:55:50PM -0700, Adrian Klaver wrote: > >If I am following, this duplicates the information in > >companies.client_code_increment, in that they both return the last > >non-user code. Of course this assumes, as David mentioned, that the > >client is not usin

Re: [GENERAL] Function PostgreSQL 9.2

On 04/20/2016 03:33 PM, drum.lu...@gmail.com wrote: If I am following, this duplicates the information in companies.client_code_increment, in that they both return the last non-user code. Of course this assumes, as David mentioned, that the client is not using a numeric code sys

Re: [GENERAL] Function PostgreSQL 9.2

On 4/20/2016 3:33 PM, drum.lu...@gmail.com wrote: The customer can add any value into users.code: code CHARACTER VARYING, what if he puts in a non-unique value ? But he also can let it blank/null if he wants to. That's when the trigger do its job.. Put a value (starting in 1000) in t

Re: [GENERAL] Function PostgreSQL 9.2

> > > > If I am following, this duplicates the information in > companies.client_code_increment, in that they both return the last non-user > code. Of course this assumes, as David mentioned, that the client is not > using a numeric code system. Then you are left trying to figure whether a > number

Re: [GENERAL] Function PostgreSQL 9.2

On 04/20/2016 02:51 PM, drum.lu...@gmail.com wrote: Well.. will try ONE more time then. 1 - The customer can add any value into users.code column 2 - The customer can chose between *add or no**t* add the value on users.code column 3 - If users.code is null (because the customer's chosen

Re: [GENERAL] Function PostgreSQL 9.2

On Wed, Apr 20, 2016 at 2:51 PM, drum.lu...@gmail.com wrote: > 1 - The customer can add any value into users.code column > 2 - The customer can chose between *add or no**t* add the value on > users.code column > 3 - If users.code is null (because the customer's chosen not to add any > value in th

Re: [GENERAL] Function PostgreSQL 9.2

On 4/20/2016 2:51 PM, drum.lu...@gmail.com wrote: 1 - The customer can add any value into users.code column 2 - The customer can chose between *add or no**t* add the value on users.code column 3 - If users.code is null (because the customer's chosen not to add any value in there), a trigger/fun

Re: [GENERAL] Function PostgreSQL 9.2

On 21 April 2016 at 09:44, Adrian Klaver wrote: > On 04/19/2016 07:34 PM, drum.lu...@gmail.com wrote: > >> Information. eg.: >> >> >> The schema for the tables. >> >> Why is not just adding a DEFAULT value to the users.code not an >> option? >> >> >> >> The customer can add their own

Re: [GENERAL] Function PostgreSQL 9.2

On 04/19/2016 07:34 PM, drum.lu...@gmail.com wrote: Information. eg.: The schema for the tables. Why is not just adding a DEFAULT value to the users.code not an option? The customer can add their own value to the users.code column. That's why I can't have a default value. That

Re: [GENERAL] Function PostgreSQL 9.2

This is such a poorly designed, hacked together “thing” – it isn’t a database, it’s someone’s idea of how to store data when they don’t know how to store data, like they moved it from Access or Excel. Just start over and design a proper relational schema with best practices and you’ll save

Re: [GENERAL] Function PostgreSQL 9.2

Just forgot to say: [...] > > >> >> Does increment_client_code relate to users or some other table, say >> clients? >> >> > nope.. there is no link between them > > If the users.code is empty/null, then the trigger has to get the last number from client_code_increment and put on the users.code

Re: [GENERAL] Function PostgreSQL 9.2

> > Information. eg.: > > The schema for the tables. > > Why is not just adding a DEFAULT value to the users.code not an option? > > The customer can add their own value to the users.code column. That's why I can't have a default value. > What the default code should be or how it is to be calc

Re: [GENERAL] Function PostgreSQL 9.2

On 04/19/2016 03:23 PM, drum.lu...@gmail.com wrote: Hi all, I've got two tables: - users - companies I'm trying to create a function that: * if users.code is empty, it gives a default value * And the increment_client_code in company should auto increment for the next client code What

Re: [GENERAL] Function PostgreSQL 9.2

On 20 April 2016 at 10:38, David G. Johnston wrote: > On Tue, Apr 19, 2016 at 3:23 PM, drum.lu...@gmail.com < > drum.lu...@gmail.com> wrote: > >> Hi all, >> >> I've got two tables: >> >> - users >> - companies >> >> I'm trying to create a function that: >> >> >>- if users.code is empty, it gi

Re: [GENERAL] Function PostgreSQL 9.2

On Tue, Apr 19, 2016 at 3:23 PM, drum.lu...@gmail.com wrote: > Hi all, > > I've got two tables: > > - users > - companies > > I'm trying to create a function that: > > >- if users.code is empty, it gives a default value >- And the increment_client_code in company should auto increment for

Re: [GENERAL] Function fixing - PostgreSQL 9.2

> > >>> *Question:* Payments in a Pending state cannot be invoiced and are excluded from the Invoice Runs section, but they are showing in the count mechanic. How can I solve this? >>> >>> ​In 9.2 you probably need to convert the count into a conditional sum: >>> >>> S

Re: [GENERAL] Function fixing - PostgreSQL 9.2

On Mon, Feb 29, 2016 at 6:14 PM, drum.lu...@gmail.com wrote: > > > On 1 March 2016 at 11:35, David G. Johnston > wrote: > >> On Mon, Feb 29, 2016 at 2:56 PM, drum.lu...@gmail.com < >> drum.lu...@gmail.com> wrote: >> >>> *Question:* >>> >>> Payments in a Pending state cannot be invoiced and are e

Re: [GENERAL] Function fixing - PostgreSQL 9.2

On 1 March 2016 at 11:35, David G. Johnston wrote: > On Mon, Feb 29, 2016 at 2:56 PM, drum.lu...@gmail.com < > drum.lu...@gmail.com> wrote: > >> *Question:* >> >> Payments in a Pending state cannot be invoiced and are excluded from the >> Invoice Runs section, but they are showing in the count me

Re: [GENERAL] Function fixing - PostgreSQL 9.2

On Mon, Feb 29, 2016 at 2:56 PM, drum.lu...@gmail.com wrote: > *Question:* > > Payments in a Pending state cannot be invoiced and are excluded from the > Invoice Runs section, but they are showing in the count mechanic. > > How can I solve this? > ​In 9.2 you probably need to convert the count i

Re: [GENERAL] Function error

Sachin Srivastava wrote: > In my function the problem is that global variables defined inside the > function. > These variables are visible to functions defined inside a function. > If we move these inner functions to outside of the main function, > they will lose the visibility of the global vari

Re: [GENERAL] Function error

On Wed, Jan 13, 2016 at 10:06 PM, Sachin Srivastava wrote: > Dear Albe, > > Thanks for your support !!! > > In my function the problem is that global variables defined inside the > function. These variables are visible to functions defined inside a > function. > ​Holy Crap...PostgreSQL functions

Re: [GENERAL] Function error

Dear Albe, Thanks for your support !!! In my function the problem is that global variables defined inside the function. These variables are visible to functions defined inside a function. If we move these inner functions to outside of the main function, they will lose the visibility of the global

Re: [GENERAL] Function error

On 01/13/2016 01:18 AM, Sachin Srivastava wrote: > > Dear Team, > > I am getting the below error for function, please see the bold line in > "Function code", please suggest what I will do to correct this code. > > --- > > ERROR: syntax error at or near "*" > LINE 35: SEL

Re: [GENERAL] Function error

Sachin Srivastava wrote: > I am getting the below error for function, please see the bold line in > "Function code", please suggest > what I will do to correct this code. > > --- > > ERROR: syntax error at or near "*" > LINE 35: SELECT * from logError(msg text) is >

Re: [GENERAL] Function error

Thanks Pavel for your help !!! On Fri, Jan 8, 2016 at 3:28 PM, Pavel Stehule wrote: > > > 2016-01-08 10:52 GMT+01:00 Sachin Srivastava : > >> Because I have migrated the database from Oracle to Postgres through >> ORA2PG. >> >> So how I will change it, please suggest. >> > > > http://stackoverfl

Re: [GENERAL] Function error

2016-01-08 10:52 GMT+01:00 Sachin Srivastava : > Because I have migrated the database from Oracle to Postgres through > ORA2PG. > > So how I will change it, please suggest. > http://stackoverflow.com/questions/2269156/converting-from-oracle-join-to-postgres-join > On Fri, Jan 8, 2016 at 2:54 P

Re: [GENERAL] Function error

Because I have migrated the database from Oracle to Postgres through ORA2PG. So how I will change it, please suggest. On Fri, Jan 8, 2016 at 2:54 PM, Pavel Stehule wrote: > > > 2016-01-08 10:08 GMT+01:00 Sachin Srivastava : > >> Hi, >> >> I am also getting "syntax error for below function (just

Re: [GENERAL] Function error

2016-01-08 10:08 GMT+01:00 Sachin Srivastava : > Hi, > > I am also getting "syntax error for below function (just some lines of > function where I am getting syntax error), please suggest why? > Using Oracle's outer join syntax, not ANSI SQL syntax Regards Pavel > > > -

Re: [GENERAL] Function error

Hi, I am also getting "syntax error for below function (just some lines of function where I am getting syntax error), please suggest why? -- Image path AND mk1.subscriber_id(+)=pcat_catalog_item.subscriber_id AND cs1.subscriber_id(+)=pcat_catalog_item.subscr

Re: [GENERAL] Function error

Hi 2016-01-08 8:59 GMT+01:00 Sachin Srivastava : > Hi, > > Also there is any command to see the invalid and valid function in > postgres database. > No, Postgres is not a Oracle. All functions in database are valid. But it means some different than in Oracle. That's "all embedded SQL are syntact

Re: [GENERAL] Function error

Hi, Also there is any command to see the invalid and valid function in postgres database. Regards, SS On Fri, Jan 8, 2016 at 1:18 PM, Sachin Srivastava wrote: > Thanks Charles !!! > > On Fri, Jan 8, 2016 at 1:15 PM, Sachin Srivastava > wrote: > >> Thanks Pavel !!! >> >> On Fri, Jan 8, 2016 at

Re: [GENERAL] Function error

Thanks Charles !!! On Fri, Jan 8, 2016 at 1:15 PM, Sachin Srivastava wrote: > Thanks Pavel !!! > > On Fri, Jan 8, 2016 at 1:05 PM, Pavel Stehule > wrote: > >> Hi >> >> >> >> 2016-01-08 8:24 GMT+01:00 Sachin Srivastava : >> >>> Dear Concern, >>> >>> >>> >>> I am creating below function *“create_

Re: [GENERAL] Function error

Thanks Pavel !!! On Fri, Jan 8, 2016 at 1:05 PM, Pavel Stehule wrote: > Hi > > > > 2016-01-08 8:24 GMT+01:00 Sachin Srivastava : > >> Dear Concern, >> >> >> >> I am creating below function *“create_catexp_ss_master()” *and getting >> error as below, I have already created dependent function firs

Re: [GENERAL] Function error

Hello If I understand you correctly you have two functions create_catexp_ss_1 and create_catexp_ss_2 that you then call from create_catexp_master. If so then you probably need to change the call to them: > -- Exposure for single supplier without category filtering >

Re: [GENERAL] Function error

Hi 2016-01-08 8:24 GMT+01:00 Sachin Srivastava : > Dear Concern, > > > > I am creating below function *“create_catexp_ss_master()” *and getting > error as below, I have already created dependent function firstly > successfully *(“create_catexp_ss_1” and “create_catexp_ss_2”) *but still > gettin

Re: [GENERAL] Function in PostgreSQL

Sachin Srivastava wrote: > Do you know if it is possible to create a function in postgres that has > errors in the syntax of the > creation sql? It would help our conversion process if we could get all of our > various functions > present in postgres even if they aren’t in 100% working shape. Is

Re: [GENERAL] function null composite behavior

On Mon, Oct 26, 2015 at 3:34 PM, Rikard Pavelic wrote: > On Sun, 25 Oct 2015 22:31:03 +0100 > Rikard Pavelic wrote: > > > I assume there is no way to get sql like result from plpgsql > > function? > > I should try harder ;) > > Managed to get it working using array and array_agg. > > Yeah, tha

Re: [GENERAL] function null composite behavior

On Sun, 25 Oct 2015 22:31:03 +0100 Rikard Pavelic wrote: > I assume there is no way to get sql like result from plpgsql > function? I should try harder ;) Managed to get it working using array and array_agg. Regards, Rikard -- Rikard Pavelic https://dsl-platform.com/ http://templater.info/

Re: [GENERAL] function returning a merge of the same query executed X time

27; (pgsql-general@postgresql.org) Objet : Re: [GENERAL] function returning a merge of the same query executed X time Can you not just CROSS JOIN it to generate_series(1, 8)? On 22 April 2015 at 14:14, Marc-André Goderre wrote: Hi all, I'm having difficulties to  create a function that s

Re: [GENERAL] function returning a merge of the same query executed X time

On 04/22/2015 06:14 AM, Marc-André Goderre wrote: Hi all, I'm having difficulties to create a function that should execute X time the same query and return their results as a single table. I tried this way but it don't work : Thanks to help. See comments inline. create function cm_get_json

Re: [GENERAL] function returning a merge of the same query executed X time

Can you not just CROSS JOIN it to generate_series(1, 8)? On 22 April 2015 at 14:14, Marc-André Goderre wrote: > Hi all, > I'm having difficulties to create a function that should execute X time > the same query and return their results as a single table. > I tried this way but it don't work :

Re: [GENERAL] function returning a merge of the same query executed X time

On 4/22/15 8:14 AM, Marc-André Goderre wrote: select row_to_json(q) from (select row_number() over() as id, sum(cost) as total_cost,sum(length) as total_length,json_agg(row_to_json(r)) as data from (select * from cm_get_loop_route_4(2, 10, -73.597070, 45.544083))r)q

Re: [GENERAL] function to send email with query results

On 4/17/2015 10:30 PM, Suresh Raja wrote: I'm looking to write a function to send email with result of a query. Is it possible to send email with in a function. Any help is appreciated. I would do that in an application, not a pl sql function. make a query, fetch the results, forma

Re: [GENERAL] function indexes, index only scan and sorting

Jonathan Vanasco writes: > Thank you so much for posting this test. > I got a seq scan on my local machine, so I checked the version... still > running 9.2.4. > I tried it on production (which is 9.3.x) and got the same result as you. Hmm, well, I get the same result from 9.2.9, as well as ever

Re: [GENERAL] function indexes, index only scan and sorting

On Dec 12, 2014, at 4:58 PM, Tom Lane wrote: > regression=# create table tt (f1 int, f2 text); > CREATE TABLE > regression=# create index on tt (lower(f2)); > CREATE INDEX > regression=# explain select * from tt order by lower(f2); > QUERY PLAN

Re: [GENERAL] function indexes, index only scan and sorting

Jonathan Vanasco writes: > Am I correct in observing that the value of a function index can't be used > for sorting ? No ... regression=# create table tt (f1 int, f2 text); CREATE TABLE regression=# create index on tt (lower(f2)); CREATE INDEX regression=# explain select * from tt order by lowe

  1   2   3   4   5   6   >