Postgres Pro
Downloads
Home   >   mailing lists

Thread: SELECTing from a function where i don't want the results

SELECTing from a function where i don't want the results

From
Wells Oliver
Date:
Vanilla SQL script calls a plpgsql function to delete some number of rows from three tables:

SELECT mydelete(r) FROM sometable;

Where sometable contains maybe 100+ records. This causes the results from the function (integer of number of rows removed) to be displayed in the output, like you'd kinda expect with a SELECT call, except I don't want to see it all, I just want the function quietly executed and rows removed.

Can I accomplish this?

--

Re: SELECTing from a function where i don't want the results

From
Holger Jakobs
Date:

You could combine the result with a NULL value, as any operations with NULL result in NULL.

SELECT mydelete(r) + NULL FROM sometable;

Am 08.07.20 um 00:34 schrieb Wells Oliver:
Vanilla SQL script calls a plpgsql function to delete some number of rows from three tables:

SELECT mydelete(r) FROM sometable;

Where sometable contains maybe 100+ records. This causes the results from the function (integer of number of rows removed) to be displayed in the output, like you'd kinda expect with a SELECT call, except I don't want to see it all, I just want the function quietly executed and rows removed.

Can I accomplish this?

-- 
-- 
Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012

Re: SELECTing from a function where i don't want the results

From
"David G. Johnston"
Date:
On Tuesday, July 7, 2020, Holger Jakobs <holger@jakobs.com> wrote:

You could combine the result with a NULL value, as any operations with NULL result in NULL.

SELECT mydelete(r) + NULL FROM sometable;

So now you have 100 rows containing null (assuming that indeed function result + unknown means something and you don’t just get an error) which is no better than what is the probably 100 rows of void output the OP is complaining about.

David J.

Re: SELECTing from a function where i don't want the results

From
Wells Oliver
Date:
Yeah. I just kinda want a silent SELECT since the function I'm calling just deletes, and I don't care about the output.

I'm guessing this is just something I need to get over.

On Tue, Jul 7, 2020 at 5:07 PM David G. Johnston <david.g.johnston@gmail.com> wrote:
On Tuesday, July 7, 2020, Holger Jakobs <holger@jakobs.com> wrote:

You could combine the result with a NULL value, as any operations with NULL result in NULL.

SELECT mydelete(r) + NULL FROM sometable;

So now you have 100 rows containing null (assuming that indeed function result + unknown means something and you don’t just get an error) which is no better than what is the probably 100 rows of void output the OP is complaining about.

David J.



--

Re: SELECTing from a function where i don't want the results

From
"David G. Johnston"
Date:
On Tuesday, July 7, 2020, Wells Oliver <wells.oliver@gmail.com> wrote:
Vanilla SQL script calls a plpgsql function to delete some number of rows from three tables:

SELECT mydelete(r) FROM sometable;

Where sometable contains maybe 100+ records. This causes the results from the function (integer of number of rows removed) to be displayed in the output, like you'd kinda expect with a SELECT call, except I don't want to see it all, I just want the function quietly executed and rows removed.

Can I accomplish this?


Pure SQL, no, you cannot just ignore the output.  You can perform post-processing (via CTE/WITH) to reduce how much is printed (aggregates). If you are using psql you can send it to /dev/null.  You could use a DO block and (kinda) ignore the result (SQL) and/or stick it into a throw-away variable (plpgsql).

David J.

Re: SELECTing from a function where i don't want the results

From
Wells Oliver
Date:
ha, the CTE approach to only get one line of output versus however many hundreds of rows were used for the delete is perfect. Thanks.

On Tue, Jul 7, 2020 at 5:18 PM David G. Johnston <david.g.johnston@gmail.com> wrote:
On Tuesday, July 7, 2020, Wells Oliver <wells.oliver@gmail.com> wrote:
Vanilla SQL script calls a plpgsql function to delete some number of rows from three tables:

SELECT mydelete(r) FROM sometable;

Where sometable contains maybe 100+ records. This causes the results from the function (integer of number of rows removed) to be displayed in the output, like you'd kinda expect with a SELECT call, except I don't want to see it all, I just want the function quietly executed and rows removed.

Can I accomplish this?


Pure SQL, no, you cannot just ignore the output.  You can perform post-processing (via CTE/WITH) to reduce how much is printed (aggregates). If you are using psql you can send it to /dev/null.  You could use a DO block and (kinda) ignore the result (SQL) and/or stick it into a throw-away variable (plpgsql).

David J.



--

Re: SELECTing from a function where i don't want the results

From
Ron
Date:
Your original question says that the function returns the number of rows deleted.  That's one line.  Now you're saying that it returns one row per table-row deleted.  Which is it?

On 7/7/20 7:21 PM, Wells Oliver wrote:
ha, the CTE approach to only get one line of output versus however many hundreds of rows were used for the delete is perfect. Thanks.

On Tue, Jul 7, 2020 at 5:18 PM David G. Johnston <david.g.johnston@gmail.com> wrote:
On Tuesday, July 7, 2020, Wells Oliver <wells.oliver@gmail.com> wrote:
Vanilla SQL script calls a plpgsql function to delete some number of rows from three tables:

SELECT mydelete(r) FROM sometable;

Where sometable contains maybe 100+ records. This causes the results from the function (integer of number of rows removed) to be displayed in the output, like you'd kinda expect with a SELECT call, except I don't want to see it all, I just want the function quietly executed and rows removed.

Can I accomplish this?


Pure SQL, no, you cannot just ignore the output.  You can perform post-processing (via CTE/WITH) to reduce how much is printed (aggregates). If you are using psql you can send it to /dev/null.  You could use a DO block and (kinda) ignore the result (SQL) and/or stick it into a throw-away variable (plpgsql).

David J.



--

--
Angular momentum makes the world go 'round.

Re: SELECTing from a function where i don't want the results

From
Wells Oliver
Date:
Yes: it returns one row per record for deletion with a deleted row count, and is called for hundreds (sometimes thousands) of records.

On Tue, Jul 7, 2020 at 5:45 PM Ron <ronljohnsonjr@gmail.com> wrote:
Your original question says that the function returns the number of rows deleted.  That's one line.  Now you're saying that it returns one row per table-row deleted.  Which is it?

On 7/7/20 7:21 PM, Wells Oliver wrote:
ha, the CTE approach to only get one line of output versus however many hundreds of rows were used for the delete is perfect. Thanks.

On Tue, Jul 7, 2020 at 5:18 PM David G. Johnston <david.g.johnston@gmail.com> wrote:
On Tuesday, July 7, 2020, Wells Oliver <wells.oliver@gmail.com> wrote:
Vanilla SQL script calls a plpgsql function to delete some number of rows from three tables:

SELECT mydelete(r) FROM sometable;

Where sometable contains maybe 100+ records. This causes the results from the function (integer of number of rows removed) to be displayed in the output, like you'd kinda expect with a SELECT call, except I don't want to see it all, I just want the function quietly executed and rows removed.

Can I accomplish this?


Pure SQL, no, you cannot just ignore the output.  You can perform post-processing (via CTE/WITH) to reduce how much is printed (aggregates). If you are using psql you can send it to /dev/null.  You could use a DO block and (kinda) ignore the result (SQL) and/or stick it into a throw-away variable (plpgsql).

David J.



--

--
Angular momentum makes the world go 'round.


--

Re: SELECTing from a function where i don't want the results

From
Scott Ribe
Date:
> On Jul 7, 2020, at 6:48 PM, Wells Oliver <wells.oliver@gmail.com> wrote:
>
> Yes: it returns one row per record for deletion with a deleted row count, and is called for hundreds (sometimes
thousands)of records. 

Why not write a function that works the way you want?

Re: SELECTing from a function where i don't want the results

From
Wells Oliver
Date:
Sorry guys, it works exactly the way I want, just wanted to suppress its output when called for thousands of rows. Apologies if I wasn't clear enough.

On Tue, Jul 7, 2020 at 6:07 PM Scott Ribe <scott_ribe@elevated-dev.com> wrote:
> On Jul 7, 2020, at 6:48 PM, Wells Oliver <wells.oliver@gmail.com> wrote:
>
> Yes: it returns one row per record for deletion with a deleted row count, and is called for hundreds (sometimes thousands) of records.

Why not write a function that works the way you want?


--

Re: SELECTing from a function where i don't want the results

From
Ron
Date:
Especially since deleting one row at a time is very slow.

On 7/7/20 8:06 PM, Scott Ribe wrote:
>> On Jul 7, 2020, at 6:48 PM, Wells Oliver <wells.oliver@gmail.com> wrote:
>>
>> Yes: it returns one row per record for deletion with a deleted row count, and is called for hundreds (sometimes
thousands)of records.
 
> Why not write a function that works the way you want?

-- 
Angular momentum makes the world go 'round.

Re: SELECTing from a function where i don't want the results

From
Scott Ribe
Date:
> On Jul 7, 2020, at 7:14 PM, Wells Oliver <wells.oliver@gmail.com> wrote:
>
> Sorry guys, it works exactly the way I want, just wanted to suppress its output when called for thousands of rows.
Apologiesif I wasn't clear enough. 

It seems that it doesn't work the way you want, because it can output thousands of rows ;-)

Re: SELECTing from a function where i don't want the results

From
"David G. Johnston"
Date:
On Tuesday, July 7, 2020, Scott Ribe <scott_ribe@elevated-dev.com> wrote:
> On Jul 7, 2020, at 7:14 PM, Wells Oliver <wells.oliver@gmail.com> wrote:
>
> Sorry guys, it works exactly the way I want, just wanted to suppress its output when called for thousands of rows. Apologies if I wasn't clear enough.

It seems that it doesn't work the way you want, because it can output thousands of rows ;-)

You all may want to re-read the original post before commenting further.  I’m finding nothing particularly unusual about this setup.  Sure, multiple delete using commands or cascade delete may be a bit more performant if the api described in the OP is sufficiently fast it is definitely the easiest to program against.

David J.

Re: SELECTing from a function where i don't want the results

From
Jim Gmail
Date:
Perhaps the OP could supply the function definition to make things clearer.

Sent from my iPhone

On 8 Jul 2020, at 11:24, Scott Ribe <scott_ribe@elevated-dev.com> wrote:

>> On Jul 7, 2020, at 7:14 PM, Wells Oliver <wells.oliver@gmail.com> wrote:
>>
>> Sorry guys, it works exactly the way I want, just wanted to suppress its output when called for thousands of rows.
Apologiesif I wasn't clear enough. 
>
> It seems that it doesn't work the way you want, because it can output thousands of rows ;-)
>
>
>

Re: SELECTing from a function where i don't want the results

From
hubert depesz lubaczewski
Date:
On Tue, Jul 07, 2020 at 03:34:31PM -0700, Wells Oliver wrote:
> Vanilla SQL script calls a plpgsql function to delete some number of rows
> from three tables:
> 
> SELECT mydelete(r) FROM sometable;

select count( mydelete(r) ) from sometable;

this will return just one row regardless of how many rows are in
sometable.

Best regards,

depesz

SELECTing from a function where i don't want the results

From
Marlene Villanueva
Date:
Please send me a link to my actual open bank account.