Обсуждение: Only target lines of text
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
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.
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
Tom,
Yes, that's it, thanks!
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
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.MikeOn 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
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
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