agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedOnly target lines of text
7+ messages / 3 participants
[nested] [flat]
* Only target lines of text
@ 2016-11-15 01:19 Michael Moore <michaeljmoore@gmail.com>
0 siblings, 2 replies; 7+ messages in thread
From: Michael Moore @ 2016-11-15 01:19 UTC (permalink / raw)
To: pgsql-sql
how can I change this so that it only shows me the lines of text containing
the matching string?
SELECT
proname AS functionname,
prosrc AS source
FROM
pg_proc
WHERE
prosrc ~* 'ypoi_db';
Thanks,
Mike
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Only target lines of text
@ 2016-11-15 01:36 Rob Sargent <robjsargent@gmail.com>
parent: Michael Moore <michaeljmoore@gmail.com>
1 sibling, 0 replies; 7+ messages in thread
From: Rob Sargent @ 2016-11-15 01:36 UTC (permalink / raw)
To: pgsql-sql
On 11/14/2016 06:19 PM, Michael Moore wrote:
> how can I change this so that it only shows me the lines of text
> containing the matching string?
>
> SELECT
> proname AS functionname,
> prosrc AS source
> FROM
> pg_proc
> WHERE
> prosrc ~* 'ypoi_db';
>
> Thanks,
> Mike
>
>
substring with regexp captures.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Only target lines of text
@ 2016-11-15 01:57 Tom Lane <tgl@sss.pgh.pa.us>
parent: Michael Moore <michaeljmoore@gmail.com>
1 sibling, 1 reply; 7+ messages in thread
From: Tom Lane @ 2016-11-15 01:57 UTC (permalink / raw)
To: Michael Moore <michaeljmoore@gmail.com>; +Cc: pgsql-sql
Michael Moore <michaeljmoore@gmail.com> writes:
> how can I change this so that it only shows me the lines of text containing
> the matching string?
> SELECT
> proname AS functionname,
> prosrc AS source
> FROM
> pg_proc
> WHERE
> prosrc ~* 'ypoi_db';
You mean you just want part of each prosrc entry? Try something like
select * from
(select proname, unnest(string_to_array(prosrc, '\n')) as l from pg_proc) ss
where l ~* 'ypoi_db';
regards, tom lane
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Only target lines of text
@ 2016-11-15 03:07 Michael Moore <michaeljmoore@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 7+ messages in thread
From: Michael Moore @ 2016-11-15 03:07 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
Tom,
Yes, that's it, thanks!
And thanks Rob.
Mike
On Mon, Nov 14, 2016 at 5:57 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Michael Moore <michaeljmoore@gmail.com> writes:
> > how can I change this so that it only shows me the lines of text
> containing
> > the matching string?
>
> > SELECT
> > proname AS functionname,
> > prosrc AS source
> > FROM
> > pg_proc
> > WHERE
> > prosrc ~* 'ypoi_db';
>
> You mean you just want part of each prosrc entry? Try something like
>
> select * from
> (select proname, unnest(string_to_array(prosrc, '\n')) as l from
> pg_proc) ss
> where l ~* 'ypoi_db';
>
> regards, tom lane
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Only target lines of text
@ 2016-11-15 22:58 Michael Moore <michaeljmoore@gmail.com>
parent: Michael Moore <michaeljmoore@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Michael Moore @ 2016-11-15 22:58 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
It does not seem to be finding the \n delimiter: (I've also tried \r with
the same result)
lcd1_dev=> select * from
lcd1_dev-> (select proname, unnest(string_to_array(prosrc, '\n')) as l
from pg_proc) ss
lcd1_dev-> where l ~* 'ypoi_db';
proname |
l
-----------------+---------------------------------------------------------------------------------------------------------------------
fgeteduprograms |
| -- ⌐ Copyright QuinStreet, Inc., 2016. All rights
reserved.
| -- Author: Michael J. Moore
| -- Date: Nov. 1, 2016\r
| DECLARE \r
| --p_edu_subj_hiers text :=
'MATHEMATICS,ENGLISH-LITERATURE,WEBDESIGN,POI_ONLY,BUSINESS,EDUCATION,FASHION,LEGAL';\r
| poi_db_values_table_cnt bigint := 0;\r
| p_dealer_keys_arr text :=
'{'||not_null(p_dealer_keys)||'}';\r
| p_service_keys_arr text :=
'{'||not_null(p_service_keys)||'}';\r
| p_edu_subj_hiers_arr text :=
'{'||not_null(p_edu_subj_hiers)||'}';\r
On Mon, Nov 14, 2016 at 7:07 PM, Michael Moore <michaeljmoore@gmail.com>
wrote:
> Tom,
> Yes, that's it, thanks!
> And thanks Rob.
> Mike
>
>
> On Mon, Nov 14, 2016 at 5:57 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
>> Michael Moore <michaeljmoore@gmail.com> writes:
>> > how can I change this so that it only shows me the lines of text
>> containing
>> > the matching string?
>>
>> > SELECT
>> > proname AS functionname,
>> > prosrc AS source
>> > FROM
>> > pg_proc
>> > WHERE
>> > prosrc ~* 'ypoi_db';
>>
>> You mean you just want part of each prosrc entry? Try something like
>>
>> select * from
>> (select proname, unnest(string_to_array(prosrc, '\n')) as l from
>> pg_proc) ss
>> where l ~* 'ypoi_db';
>>
>> regards, tom lane
>>
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Only target lines of text
@ 2016-11-15 23:04 Tom Lane <tgl@sss.pgh.pa.us>
parent: Michael Moore <michaeljmoore@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Tom Lane @ 2016-11-15 23:04 UTC (permalink / raw)
To: Michael Moore <michaeljmoore@gmail.com>; +Cc: pgsql-sql
Michael Moore <michaeljmoore@gmail.com> writes:
> It does not seem to be finding the \n delimiter: (I've also tried \r with
> the same result)
Ah, sorry, that should have been E'\n'.
regards, tom lane
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Only target lines of text
@ 2016-11-15 23:10 Michael Moore <michaeljmoore@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 7+ messages in thread
From: Michael Moore @ 2016-11-15 23:10 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
Thanks Tom
On Tue, Nov 15, 2016 at 3:04 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Michael Moore <michaeljmoore@gmail.com> writes:
> > It does not seem to be finding the \n delimiter: (I've also tried \r
> with
> > the same result)
>
> Ah, sorry, that should have been E'\n'.
>
> regards, tom lane
>
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2016-11-15 23:10 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2016-11-15 01:19 Only target lines of text Michael Moore <michaeljmoore@gmail.com>
2016-11-15 01:36 ` Rob Sargent <robjsargent@gmail.com>
2016-11-15 01:57 ` Tom Lane <tgl@sss.pgh.pa.us>
2016-11-15 03:07 ` Michael Moore <michaeljmoore@gmail.com>
2016-11-15 22:58 ` Michael Moore <michaeljmoore@gmail.com>
2016-11-15 23:04 ` Tom Lane <tgl@sss.pgh.pa.us>
2016-11-15 23:10 ` Michael Moore <michaeljmoore@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox